DAX - Column content based on last Date slicer selection

[Anonymous]

Hello guys,

I'm trying to create a column in the fSales with the last status of the order according to the last selected date. I have a fact Sales, Calendar and a fact Historical Sales

fSales

id_order	sales date
ab123	01/01/19
ab124	02/01/19
ab125	02/01/19
ab126	03/01/19

fHistoricalSales

id_Order	DT_Status	Status
ab123	01/01/19	NOR
ab123	15/01/19	CAN
ab124	02/01/19	NOR
ab124	13/01/19	CAN
ab125	03/01/19	NOR
ab125	14/01/19	CAN
ab125	20/01/19	REC
ab126	03/01/19	NOR

If I select the range 01/01/19 to 12/01/19 the .NewStatusOrder of ab124 should be NOR, but if I select to 13/01/19 it should be CAN.

I'm trying somithing like that, but it's not working:

.NewStatusOrder:= CALCULATE(
	 MAX(fHistorialSales[Status]) ;
 FILTER(
			ALLEXCEPT(fHistoricalSales;fHistorical[ID_ORDER]) ; 
				fHistoricalSales[DT_Status] = CALCULATE(MAX(fHistoricalSales[DT_Status]); 
							FILTER( fHistoricalSales; fHistorialSales[id_order] = EARLIER( fSales[ID_Order]) && fHistoricalSales[DT_Status] <= MAX(Date[DATE])))))

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

NewStatusOrder =
VAR LatestDT_Status =
    CALCULATE (
        MAX ( fHistoricalSales[DT_Status] ),
        FILTER (
            ALLSELECTED ( fHistoricalSales ),
            fHistoricalSales[id_Order] = SELECTEDVALUE ( fHistoricalSales[id_Order] )
        )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( fHistoricalSales[Status] ),
        FILTER ( fHistoricalSales, fHistoricalSales[DT_Status] = LatestDT_Status )
    )

Best regards,

Yuliana Gu

[Anonymous]

Hello @v-yulgu-msft , thanks for helping me.

The problem is that I have a 1 conection with Calendar[Date] and fSales[SalesDate] and a secondary connection with Calendar[Date] and fHistoricalSales[DT_Status]. Do you think using USERELATIONSHIP could help?

[v-yulgu-msft]

Hi @Anonymous ,

Make sure there existing an active relationship between fSales[id_order] and fHistoricalSales[id_Order], so, the relationships should be like below, above measure still works.

Best regards,

Yuliana Gu

[ElenaN]

Hello,

You can try this:

NewStatusOrder = 
VAR LastSelectedDate = LASTDATE(ALLSELECTED('Calendar'[Date]))
VAR LastOrderDate = CALCULATE(MAX(fHistoricalSales[DT_Status]), fHistoricalSales[DT_Status] <= LastSelectedDate)
RETURN
CALCULATE(MAX(fHistoricalSales[Status]), FILTER(fHistoricalSales, fHistoricalSales[DT_Status] = LastOrderDate))

Regards,

ElenaN

[Anonymous]

Hello @ElenaN , the LastSelectionDate is not getting the filter selection ;(