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 🙂

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hello barritown, thanks a lot for your help.
Your purpose doesn't work because COUNT(ID) is a calculated measure already (Measure COUNT(ID) = Count(products[ID])
I've tried to set a column for this count but it doesn't work too.

 

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

Error : The MAX function in DAX only accepts a column reference as an argument

 

 



 

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 🙂