Difference between last row

[Anonymous]

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 between days without any logical chronology between each day?
I just want to calculate the change with the previous row.

Thanks in advance,

[Anonymous]

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]

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

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]

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