topic Re: Running Total with multiple Measures in DAX Commands and Tips

Running Total with multiple Measures

vanSiq01 — Fri, 05 May 2023 21:35:24 GMT

Hi!!

I've been struggling to solve this problem for a few days now. I wanted to calculate a moving sum with multiple measures. I've create an Excel example for it: Excel Example

In other words, I have a measure called Measure1 and I wanted to accumulate the values of Measure2 starting from it.

However, this accumulation has to follow the operation highlighted in column C. That is, the lines Start Value and Breakdown 1 should be added and from there all the following values should be subtracted.

In the end, it would be something like this: (Start Value + Breakdown 1) - Sum of all other Breakdowns.

The only requirement that need to be done only with measures and not with calculated columns.

Re: Running Total with multiple Measures

tamerj1 — Fri, 05 May 2023 21:04:38 GMT

Hi vanSiq01

Please try

Accumulated Value =
SUMX (
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
IF ( 'Table'[Operation] = "+", 1, -1 ) * 'Table'[Base Value]
)

Re: Running Total with multiple Measures

vanSiq01 — Fri, 05 May 2023 21:58:09 GMT

Hi tamerj1 !

Thank you for reply. Your code is a partial solution for this. Note that, I need from Measure1 (Start Value) add/subtract values of Measures 2 (Breakdowns).

Your code works for only one measure... but I need to start from one measure and add/subtract with the other one.

Re: Running Total with multiple Measures

tamerj1 — Sat, 06 May 2023 09:33:27 GMT

vanSiq01
I followed what you have presented. You have presented everything in a table but you never mentioned whether it is a data table or a table visual. You did not provide any description of your data, rather you just presented a data in an excel sheet! Therefore, I assumed that this is a data table. Based on that, I have proposed my solution. If I have the wrong understanding then please clarify.