Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Last values change per category

I have data from UB. They release info twice a week. Highs and Lows for each category (1-2, 2-3...)

 

I have it in excel and I am trying to replicate it on Power BI.
UB

 

 

 

 

 

 

For the Change column I tried:

 

Change Previous Day =
VAR PDChange =
CALCULATE (
[Last value],
DATEADD ( 'UB Data'[Date], - 1, day ))
RETURN
[Last value] - PDChange
 
But it didn't work. Any help is appreciated. I've attached the pbix to this message.

 

Urner Barry.pbix

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI Anonymous,

    You can try to use the following measure formula to get the difference between current and previous(not continues) based on the current category group:

    Change Previous Day =
VAR currDate =
    MAX ( 'UB Data'[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( 'UB Data'[Date] ),
        FILTER ( ALLSELECTED ( 'UB Data' ), [Date] < currDate ),
        VALUES ( 'UB Data'[Size] )
    )
VAR PDChange =
    CALCULATE (
        [Last value],
        FILTER ( ALLSELECTED ( 'UB Data' ), [Date] = prevDate ),
        VALUES ( 'UB Data'[Size] )
    )
RETURN
    [Last value] - PDChange

    Regards,

    Xiaoxin Sheng

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Th formula should be like this. Mark calendar as a date table, use period from calendar table in slicer and visual

     

    Last value = 
CALCULATE (
    SUM ( 'UB Data'[Price] ),PREVIOUSDAY('Calendar'[Date]))

     

    but if the dates are not continuous 

    then

     

    example

    Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

     

    Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =calculate( max(Sales[Sales Date]), FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])))))

     

     

    Day Intelligence - Last day, last non continous day
    https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I appreciated your time. Thanks.

       

      But my issue was not in finding the last day. The dates are not continuous, and I need to find the difference between the last day and the previous day (it varies between 2 and 6 days before). That is where I am struggling.

       

      Urner Barry.pbix

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI Anonymous,

        You can try to use the following measure formula to get the difference between current and previous(not continues) based on the current category group:

        Change Previous Day =
VAR currDate =
    MAX ( 'UB Data'[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( 'UB Data'[Date] ),
        FILTER ( ALLSELECTED ( 'UB Data' ), [Date] < currDate ),
        VALUES ( 'UB Data'[Size] )
    )
VAR PDChange =
    CALCULATE (
        [Last value],
        FILTER ( ALLSELECTED ( 'UB Data' ), [Date] = prevDate ),
        VALUES ( 'UB Data'[Size] )
    )
RETURN
    [Last value] - PDChange

        Regards,

        Xiaoxin Sheng