Rolling total not working for delta calculated by measure

Anonymous — Mon, 03 May 2021 12:59:35 GMT

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.

Re: Rolling total not working for delta calculated by measure

amitchandak — Mon, 03 May 2021 13:35:25 GMT

Anonymous , Try a measure like

calculate(sumx(values(DateTable[Year Qtr]),[ValueDelta]), filter(AllSelected(DateTable), DateTable[Date]<=max(DateTable[Date])))

Re: Rolling total not working for delta calculated by measure

Anonymous — Mon, 03 May 2021 13:44:08 GMT

This works! thank you so much, was really struggling.

topic Rolling total not working for delta calculated by measure in DAX Commands and Tips

Rolling total not working for delta calculated by measure

Re: Rolling total not working for delta calculated by measure

Re: Rolling total not working for delta calculated by measure