topic Re: Difference between last row in DAX Commands and Tips

Difference between last row

Anonymous — Mon, 17 Jul 2023 12:36:26 GMT

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,

Re: Difference between last row

barritown — Mon, 17 Jul 2023 15:20:56 GMT

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

Re: Difference between last row

Anonymous — Wed, 26 Jul 2023 08:31:13 GMT

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

Re: Difference between last row

barritown — Wed, 26 Jul 2023 09:00:23 GMT

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.

Re: Difference between last row

Anonymous — Wed, 26 Jul 2023 09:02:11 GMT

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 🙂