- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX - How calculate a Running Total 2 from pre...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

aparodi

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2015
09:12 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

aparodi

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-24-2015
07:10 AM

After various tests, I found a solution.

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

5 REPLIES 5

leonardmurphy

Established Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-23-2015
09:10 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-24-2015
02:28 AM

Hi Leonard

In [running tot2] i expecting:

2/3 4

2/10 12

2/17 24

2/24 37

aparodi

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-24-2015
07:10 AM

After various tests, I found a solution.

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

leonardmurphy

Established Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-24-2015
08:52 AM

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.

In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.

Highlighted
##

ndesousa

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-31-2017
05:56 PM

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