Forum Discussion
Running Total - DAX
Hi all,
I have an issue calculating running totals.
We use a matching chance metric to calculate a forecast.
Matching chance Job level = SUMX(VALUES(Table[column1]) ; CALCULATE(MAX(Table[column2])))
Now we want to calculate running totals on the level of account and date (year/month). And we made this DAX formula:
Running Total = CALCULATE(SUM(Table1[columnA]); FILTER(ALL(Table1); Table1[Date] <= MAX(Table1[Date])); VALUES(Table2[columnA]); VALUES(Table1[columnB]))
| Year | Month | Matching chance Job level | Running Total |
| 2016 | October | 1 | 1,00 |
| 2016 | November | 1,4 | 2,00 |
| 2016 | December | 0,4 | 1,90 |
| 2017 | January | 1 | 3,00 |
| 2017 | February | 0,5 | 3,10 |
| TOTAL | 4,40 | 4,40 |
Any ideas would be much appericated. Thanks for any and all help in advance!
Running Total = CALCULATE(SUM(Table1[columnA]); FILTER(ALL(Table1); Table1[Date] <= MAX(Table1[Date])),FILTER(ALLSELECTED(Table2[columnA]),Table2[columnA]=Table2[columnA]),FILTER(ALLSELECTED(Table1[columnB]),Table1[columnB]=Table1[columnB]))
Then try this way in order to make filter works
4 Replies
Formula Looks good not sure if VALUES are helping out in this case. Try remove those and see if you are able to do calculations as desired.
Running Total = CALCULATE(SUM(Table1[columnA]); FILTER(ALL(Table1); Table1[Date] <= MAX(Table1[Date])))
Thanks for your response FarhanAhmed1984. I think I need to include the VALUES because I want to filter my visual based on Account (name) and Position (status). Or do you see another option?
Running Total = CALCULATE(SUM(Table1[columnA]); FILTER(ALL(Table1); Table1[Date] <= MAX(Table1[Date])),FILTER(ALLSELECTED(Table2[columnA]),Table2[columnA]=Table2[columnA]),FILTER(ALLSELECTED(Table1[columnB]),Table1[columnB]=Table1[columnB]))
Then try this way in order to make filter works