Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Difference between last row

Date COUNT(ID) 01/01/2023 15000 05/02/2023 15100 07/02/2023 15030 08/02/2023 18000 17/03/2023 14500 Hello there, I use direct query, how can I calculate change betw...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Thanks a lot for your help barritown. I found the right code :

    AMeasure Difference =
VAR CurrentCount = CALCULATE(COUNT(products[id]))
VAR CurrentDate = LASTDATE(products[date])
VAR PreviousDate = MAXX(FILTER(ALL(products), products[date] < CurrentDate ), products[date])
VAR PreviousCount = CALCULATE(COUNT(products[id]), FILTER(ALL(products), products[date] = PreviousDate))
RETURN IF(ISBLANK(PreviousCount), BLANK(), CurrentCount - PreviousCount)

    This looks like OK for me 🙂

barritown's avatar
barritown
Solution Sage
3 years ago

Hello Anonymous, 

For me it's difficult to take another try w/o access to your data model. If you can create a PBIX containing it and some toy data and share it with me, I'll be able to try to adapt my solution for your model. 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks a lot for your help barritown. I found the right code :

AMeasure Difference =
VAR CurrentCount = CALCULATE(COUNT(products[id]))
VAR CurrentDate = LASTDATE(products[date])
VAR PreviousDate = MAXX(FILTER(ALL(products), products[date] < CurrentDate ), products[date])
VAR PreviousCount = CALCULATE(COUNT(products[id]), FILTER(ALL(products), products[date] = PreviousDate))
RETURN IF(ISBLANK(PreviousCount), BLANK(), CurrentCount - PreviousCount)

This looks like OK for me 🙂