Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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,
Solved! Go to 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 🙂
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
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.
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 🙂
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |