DAX: Running Total on Non-Date Column

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,214
Office Version
  1. 365
Platform
  1. Windows
I have a data model pivot table with a column named "2019 Actual" which shows sales amount. The column resides on the "CombinedAllYears" table. I don't technically have dates (4/1/2019, 4/2/2019, etc) in the table, just month names "Jan", "Feb" etc. I also have a "Store" slicer and want the measure to respect the slicer selection. I am having trouble creating a measure to get the running total for the "2019 Actual" measure. The measure I have listed below works pretty close to what I want. The problem is sometimes when the "2019 Actual" amount for a prior month is greater than the subsequent month then the Running Total has it displayed incorrectly (see example below), otherwise if it is not then the measure does work properly. Can someone advise on how to fix this?

This is what the pivot table shows (note the first row is the headers)

Month....2019 Actual......Running Total
Jan........$70,000...........$140,000
Feb........$60,000...........$60,000

My current measure:

2019 Actual Running Total:=CALCULATE(sumx(CombinedAllYears,CombinedAllYears[2019 Actual]),filter(ALLSELECTED(CombinedAllYears),CombinedAllYears[2019 Actual]<=Max(CombinedAllYears[2019 Actual])))
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
The pivot table example should say $130,000 for first line of the Running Total (not $140,000)
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top