Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have the following measure which calculates the delta of the average value of last quarter and the average value of the qurrent quarter. The table contains the value per day, and needs to be averaged by quarter.
ValueDelta =
VAR CURRENTQ = AVERAGE(Table[Value])
VAR PREVQ = CALCULATE(AVERAGE(Table[Value]),PREVIOUSQUARTER(DateTable[Date]))
RETURN
IF(ISBLANK(PREVQ) || ISBLANK(CURRENTQ),BLANK(),
ABS(PREVQ - CURRENTQ)
)
Resulting in the following metrics:
Year | Quarter | ValueDelta |
2022 | 1 | 970 |
2022 | 2 | 1.603 |
2022 | 3 | 946 |
2022 | 4 | 920 |
2023 | 1 | 1.073 |
2023 | 2 | 1.341 |
2023 | 3 | 915 |
2023 | 4 | 920 |
Now I'd like to calculate the rolling total for 'ValueDelta' like this:
Year | Quarter | ValueDelta | Rolling total |
2022 | 1 | 970 | |
2022 | 2 | 1.603 | 2.573 |
2022 | 3 | 946 | 3.519 |
2022 | 4 | 920 | 4.439 |
2023 | 1 | 1.073 | 5.512 |
2023 | 2 | 1.341 | 6.853 |
2023 | 3 | 915 | 7.768 |
2023 | 4 | 920 | 8.688 |
I've tried multiple suggestions online like:
https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115
https://www.daxpatterns.com/cumulative-total/
https://www.sqlbi.com/articles/computing-running-totals-in-dax/
For me these just return the same 'ValueDelta' value or blanks.
Any ideas or directions would be very helpful!
Thanks in advance.
Solved! Go to Solution.
@Anonymous , Try a measure like
calculate(sumx(values(DateTable[Year Qtr]),[ValueDelta]), filter(AllSelected(DateTable), DateTable[Date]<=max(DateTable[Date])))
@Anonymous , Try a measure like
calculate(sumx(values(DateTable[Year Qtr]),[ValueDelta]), filter(AllSelected(DateTable), DateTable[Date]<=max(DateTable[Date])))
This works! thank you so much, was really struggling.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |