cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aparodi Frequent Visitor
Frequent Visitor

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

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

 

2015-11-20_13-24-40.png

 

2015-11-20_13-22-52.png

1 ACCEPTED SOLUTION

Accepted Solutions
aparodi Frequent Visitor
Frequent Visitor

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

After various tests, I found a solution. Smiley Very Happy

 

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

 

2015-11-24_12-03-55.png

5 REPLIES 5
leonardmurphy Established Member
Established Member

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

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?

 

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
aparodi Frequent Visitor
Frequent Visitor

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

Hi Leonard

 

In [running tot2] i expecting:

 

2/3        4

2/10     12

2/17      24

2/24      37

 

 

aparodi Frequent Visitor
Frequent Visitor

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

After various tests, I found a solution. Smiley Very Happy

 

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

 

2015-11-24_12-03-55.png

leonardmurphy Established Member
Established Member

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

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.

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
Highlighted
ndesousa Frequent Visitor
Frequent Visitor

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

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 Smiley Happy