cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aparodi
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

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

View solution in original post

5 REPLIES 5
leonardmurphy Skilled Sharer
Skilled Sharer

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

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

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

View solution in original post

leonardmurphy Skilled Sharer
Skilled Sharer

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.
ndesousa Helper I
Helper I

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

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors