DAX - How calculate a Running Total 2 from previous Running Total 1

[aparodi]

Hi,

I need create a measure (Running Tot2) that calculates running total based on an earlier measure  that calculates the a previous running total (Running Tot1)

PM Sales := sum(Sales[Current Sales])
Running Tot1 = CALCULATE([PM Sales],FILTER(ALLSELECTED(DimTime[Date]),DimTime[Date] <= MAX(DimTime[Date])))
Running Tot2 = CALCULATE([Running Tot1],FILTER(ALLSELECTED(DimTime[Date]),DimTime[Date]<= max(DimTime[Date])))

Regards

[leonardmurphy]

You didn't say what you're expecting to see. I imagine you want [running tot2] to be a date behind [running tot1]. In other words, 2/3 would be blank, 2/10 would be 4, 2/17 would be 8 and 2/24 would be 12. Is that right?

[aparodi]

Hi Leonard

In [running tot2] i expecting:

2/3        4

2/10     12

2/17      24

2/24      37

[aparodi · Accepted solution]

After various tests, I found a solution. 

Running Tot2 = SUMX(FILTER(ALLSELECTED(DimTime[Date]),DimTime[Date] <= MAX((DimTime[Date]))),[Running Tot1])

[Anonymous]

Thank you so much for this post.

I had a similar scenario where I had 2 measures.

Measure 1 had values from the start of a month up to a certain date and blank values after that date.

Measure 2 picks up from where Measure 1 stopped.

I created Measure 3 to combine the 2 and when I created a cumulative value based on Measure 3, the cumulative only worked to tne end of Measure 1.

Using your SUMX formula worked 

[leonardmurphy]

Performance-wise you could find that slow because it's recursing through PM Sales. 

E.g. 2/24/2015 value of 37 is:

2/3/2015 * 4 = 4 * 4 = 16 (where 4 is the total number of rows)

2/10/2015 * 3 = 4 * 3 = 12 (where 3 is the total number of rows less 1)

2/17/2015 * 2 = 4 * 2 = 8 (where 2 is the total number of rows less 2)

2/24/2015 * 1 = 1 * 1 = 1 (where 1 is the total number of rows less 3)

=16+12+8+1

=37

Which doesn't sound so bad, but it isn't doing it as a multiplication. SUMX is an iterative function, so the number in bold is the number of times it's reading the value and adding it into the total. So the number of reads is exponential depending on the number of rows. (1000 rows = 500,500 reads). It's a sum of the first n natural numbers where n is the number of rows problem.

I don't have a better solution though...you've reached the limits of my DAX knowledge. So I'm hoping this is at least performant enough to get you by.