- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- 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: Please explain cumulative sum work principle

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

arikshtein

Frequent Visitor

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

08-10-2017
01:48 AM

So, I have the followint formula, that calculates cumulative sum for a measure

CumulativeAmount = CALCULATE ( SUM ( sheet1[amount] ), FILTER ( ALL ( calendar[Date] ), calendar[Date] <= MAX ( calendar[Date] ) ) )

This works perfect for me, but I can't understand fully how it works.

Let's say I have transactions for year 2017. The formula calculates sum of amount, but filters only the cases where Date was below the max selected date, shifting the context, so that it will include **ALL** the dates, not only the ones included in current context. But why then MAX(Calendar[Date]) is **not **2017-12-31 ? If you override current date context, and calculate MAX, it should be the highest value of a column, which is the last day of the year (if the date table was automatically generated), no ?

Why for the left part of **condition **(calendar[Date]) in filter expression shifts current context (and returns all dates from the very beginning), but the right part (Max(calendar[Date])) does not, and returns the maximum date **inside** current context ?

I have seen this method of cumulative sum calculation in many sources, but none of them explains this particular part.

Thanks !

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

v-ljerr-msft

Super Contributor

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

08-10-2017
11:29 PM

Hi @arikshtein,

There are two important points about this expression:

**The use of ALL(Date) in order to ignore the current context.** In fact, FILTER iterates over the entire table, analyzing dates that are outside of the current filter context. In this way, it will return date that are lower than or equal to the current filter.

**The comparisons of Date[DateKey] against MAX(Date[Datekey]).** When you are not familiar with DAX, these expressions look strange. However, if you recall the exact meaning of MAX , you see that it means "the maximum value of DateKey in the current context." **Because the expression is part of CALCULATE filters, it still works in the original filter context**. On the other hand, **the expression Date[DateKey] is a column name, **meaning "The value of DateKey **in the current row context** which is created by the FILTER during its iteration."

Hope it could help you better understand how it works.

Regards

3 REPLIES 3

Highlighted

v-ljerr-msft

Super Contributor

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

08-10-2017
11:29 PM

Hi @arikshtein,

There are two important points about this expression:

**The use of ALL(Date) in order to ignore the current context.** In fact, FILTER iterates over the entire table, analyzing dates that are outside of the current filter context. In this way, it will return date that are lower than or equal to the current filter.

**The comparisons of Date[DateKey] against MAX(Date[Datekey]).** When you are not familiar with DAX, these expressions look strange. However, if you recall the exact meaning of MAX , you see that it means "the maximum value of DateKey in the current context." **Because the expression is part of CALCULATE filters, it still works in the original filter context**. On the other hand, **the expression Date[DateKey] is a column name, **meaning "The value of DateKey **in the current row context** which is created by the FILTER during its iteration."

Hope it could help you better understand how it works.

Regards

Ffitzpatrick47

Regular Visitor

Re: Please explain cumulative sum work principle

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

10-13-2017
08:50 AM

I am going through this same issue on understanding, and the root of the confusion is the fact that semantically, Date[Datekey] means different things in this equation based on where it is in the equation. By itself, it means the row context, but within a filter that is within a calculate it means the filter context of the calculate. This isn't the only language where a variable means something different based on its location. Javascript's scope chain is also lexically scoped, but the rule there is really easy, you go up the scope chain, and if you can't figure out what the scope chain is, look in the debugger. Here, if it's in a "max" function it's in the filter context of the calculate, but what happens when you nest calculate functions. Further, what other functions have their own filter contexts? Sumx, sum?

I have to admit, among obiee, cognos, crystal and tableau, Dax is the hardest most inconsistent language I have ever tried to pick up. The others can be picked up in a few days, but not dax. I don't know who the target user is of the dax language, but it's not normal people. You can't just be a person of average programming ability and figure it out. Reducing 3sat to vertex cover, I figured out in 40 minutes. This calculate function is way harder for me. Congrats to all of you who have conquered it.

arikshtein

Frequent Visitor

Re: Please explain cumulative sum work principle

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

10-16-2017
02:35 PM

Thanks everyone, looks like I figured it out... I think...