Retrive Value between Changing Dates

[Anonymous]

Hi Everyone

Having some difficulties getting this one to work. I am trying to add a calculated column in DAX that will look at item numbers in a sales table and a change log table to retrieve what the status code was at the time of the order. The second table below, with the title 'Status Code at time of Order' is what I need to be for my end result. 

Thanks

Change Log

Item	Status Code	Modified Date
1	A	2020-01-01
1	B	2020-01-15
1	A	2020-01-20
1	C	2020-01-29

SalesTable

(Need to add  'Status Code at time of Order' in Calcuated DAX Column)

Item	Order Date	Status Code at time of Order
1	2020-01-06	A
1	2020-01-13	A
1	2020-01-17	B
1	2020-01-25	A
1	2020-01-29	C

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in the Sales Table.

Hope this helps.

=LOOKUPVALUE(Change_log[Status Code],Change_log[Item],Sales[Item],Change_log[Modified Date],CALCULATE(MAX(Change_log[Modified Date]),FILTER(Change_log,Change_log[Item]=EARLIER(Sales[Item])&&Change_log[Modified Date]<=EARLIER(Sales[Order Date]))))

Hope this helps.

[Anonymous]

Much appreciated @Ashish_Mathur! One other piece to this that I had found was that with duplicate dates and separate timestamps - once those were tripped out as well your solution worked perfectly. 

[parry2k]

@Anonymous set relationship between your change log and sales table on item , it will be many to many relationship, sales order filtering items table, like below (in my example change log table is called item)

Add following DAX for column

Status Code = 
VAR __recentStatus = 
CALCULATE ( 
    MAX ( 'Item'[Status Code] ), 
    TOPN ( 
        1, 
        FIlTER ( 
            VALUES ( 'Item'[Modified Date] ), 
            'Item'[Modified Date] <= EARLIER ( Sales[Order Date] ) 
        ), 
        'Item'[Modified Date], 
        DESC 
    ) 
)

RETURN __recentStatus

and here is the output