Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX - Column content based on last Date slicer selection

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_ordersales date
ab12301/01/19
ab12402/01/19
ab12502/01/19
ab12603/01/19

fHistoricalSales

id_OrderDT_StatusStatus
ab12301/01/19NOR
ab12315/01/19CAN
ab12402/01/19NOR
ab12413/01/19CAN
ab12503/01/19NOR
ab12514/01/19CAN
ab12520/01/19REC
ab12603/01/19NOR

 

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])))))
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

5 Replies

  • ElenaN's avatar
    ElenaN
    Icon for Resolver V rankResolver V
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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