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

Hi Anonymous,

If entries in your table are sorted by date and there are no duplicate dates, you could try such a measure:

In plain text for convenience:

Difference Measure = 
VAR CurrentCount = MAX ( [COUNT(ID)] )
VAR CurrentDate = MAX ( [Date] )
VAR PreviousDate = MAXX ( FILTER ( ALL ( data ), [Date] < CurrentDate ), [Date] )
VAR PreviousCount = MAXX ( FILTER ( ALL ( data ), [Date] = PreviousDate ), [COUNT(ID)] )
RETURN IF ( ISBLANK ( PreviousCount ), BLANK(), CurrentCount - PreviousCount )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian