Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])))))
Solved! Go to 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
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
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?
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
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