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

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

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

After various tests, I found a solution.

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

5 REPLIES 5
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.
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

Frequent Visitor

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

After various tests, I found a solution.

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

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

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

Thank you so much for this post.

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.

Announcements

#### Announcing the New Spanish Forum

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

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘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