Last values change per category

[Anonymous]

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

[amitchandak]

@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]

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 · Accepted solution]

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