Calculate running total

DAX DAX Calculations
1

Hello,
I am trying to create a calculated column that calculates running totals and I am not sure where my dax calculation is going wrong.

Running Total = var _Date = Sheet2[Date]

var _Account = Sheet2[Account]

return

CALCULATE(SUM(Sheet2[Value]),ALLEXCEPT(Sheet2,Sheet2[Account]),FILTER(Sheet2,Sheet2[Account]<=_Account),FILTER(Sheet2,Sheet2[Date]<=_Date))

I used the above dax but seems to not get the correct answer.

Attaching the file for reference.
Appreciate any inputs on where I am going wrong in my dax calculations.
Running Total.xlsx (11.6 KB)
RunningTotals.pbix (37.9 KB)

The expected output is in Sheet1 and pasting here too.

Date Account Value Cost Running Total
Monday,May 10, 2021 9999 0 450 24
Monday,May 10, 2021 9999 12 450 24
Monday,May 10, 2021 9999 12 320 24
Tuesday, May 11, 2021 9999 11 120 46
Tuesday, May 11, 2021 9999 11 230 46
Wednesday, May 12, 2021 9999 8 110 54
Thursday, May 13, 2021 9999 11 200 65
Friday, May 14, 2021 9999 15 200 80
Monday, May 17, 2021 9999 11 200 91
Tuesday, May 18, 2021 9999 12 300 115
Tuesday, May 18, 2021 9999 12 300 115
Wednesday, May 19, 2021 9999 20 100 135
Wednesday, May 19, 2021 1000 2500 100 2500
Wednesday, May 19, 2021 9999 20 200 155
Thursday, May 20, 2021 9999 8 300 163
Friday, May 21, 2021 9999 0 500 163
Friday, May 21, 2021 2000 11 200 11
Monday, May 24, 2021 9999 0 2 163
Monday, May 24, 2021 9999 0 10 163
Tuesday, May 25,2021 9999 12 200 175
Tuesday, May 25,2021 2000 0 110 11
Wednesday, May 26, 2021 9999 11 50 186
Thursday, May 27,2021 1000 5 60 2505
Thursday, May 27,2021 9999 10 100 196
Friday, May 28, 2021 9999 10 200 206
Friday, May 28, 2021 2000 20 100 30

Thank you!

2

@Vsb79 ,

Okay, lots of different problems here contributing to the DAX not working.

  1. When doing time intelligence calculations such as cumulative/running totals by date, you need a proper date table in your data model.

  2. The date table needs to be marked as a date table.

  3. This should be done as a measure, not as a calculated column.

  4. The DAX for the running total needs to be corrected to the following (you can substitute ALLSELECTED for ALL if you’re going to be dynamically slicing or filtering)

    Cumul Value =

     CALCULATE(
     [Total Value],
     FILTER(
         ALL( Dates ),
         Dates[Date] <= MAX( Dates[Date] )
     ),
     REMOVEFILTERS( Data[Account] ),
     REMOVEFILTERS( Data[Cost] )
 )

image

I would strongly suggest going through the Data Transformations and Modeling course in the portal. The most important element of Power BI is getting your data model set up correctly. If you fail to do that, it doesn’t matter how skillful you are with DAX – it’s likely your calculations will not return accurate results even if your DAX is correct.

I hope this is helpful. Full solution file attached below.

– Brian
eDNA Forum – Running Total Solution.pbix (83.2 KB)

3

Hi @Vsb79

I noticed 2 issues in excel file. Attached screen shot below

Try this measure

image
image1364×347 26.4 KB

image
image494×643 18.6 KB

image
image779×672 16.8 KB

4

Thank you very much Brian for your suggestions. I will go through the Data Transformations and Modeling course.
I do have the date table in my model. I did not get to create it in the mockup data.

5

Thank you for pointing out Rajesh @Rajesh . Extending this problem, I have to calculate the sum of the max value of a column. Attaching the excel and the PBIX for reference
Running Total.xlsx (14.5 KB)
SumofMaxValues.pbix (75.7 KB)
. I have a column “Value” and it has multiple records for the same day with same value. But when calculating rolling sum, I need to pick the max value for the date and add up the totals for the dates selected. The way to go about is calculating the max value for each day and do a sumx on the max values. I was able to get to the point where I found the max value for the dates selected. But to get the totals of these max values, I am stuck. Appreciate your inputs. I tried your measure but it gives blank values for some of the date ranges.

For example

image
image1064×199 3.77 KB

Basically, calculating the rolling sum of the max values of the date range selected is what I am trying to calculate. The starting date of the date slicer should not change the values but the values should change with the end date of the date slicer.

Thank you
Lakshmi