topic "Sales Running Total"... start date not being adhered to in DAX Commands and Tips

"Sales Running Total"... start date not being adhered to

michellepace — Wed, 02 Sep 2020 15:57:50 GMT

Hi there,

The Problem: I am busy learning DAX. I worked through this Cummulative Total DAX PATTERN article. Whilst it works fine provided I select the initial date in my dataset... if I select anything later (such as 02/01/2018) the values before this date are still summed up.

My Question: How do I modify this code such that the start date is adhered to? I attempted to add a second condition Dates[Date]>=FirstVisibleDate in the second argument of Calculate, but that really didn't work as intended.

Many thanks,

Michelle

Re: "Sales Running Total"... start date not being adhered to

Greg_Deckler — Wed, 02 Sep 2020 16:16:44 GMT

michellepace - Can you post sample data as text so that I can recreate?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: "Sales Running Total"... start date not being adhered to

michellepace — Wed, 02 Sep 2020 16:30:26 GMT

> 1. Sample data as text, use the table tool in the editing bar
DATE|SALES
01/01/2018|60507
02/01/2018|69471
03/01/2018|100845

> 2. Expected output from sample data

> 3. Explanation in words of how to get from 1. to 2.
Whilst it works fine provided I select the initial date in my dataset... if I select anything later (such as 02/01/2018) the values before this date are still summed up.

Re: "Sales Running Total"... start date not being adhered to

amitchandak — Wed, 02 Sep 2020 16:45:17 GMT

michellepace , try like

Cumm Sales = CALCULATE(max(Sales[Purchase Date]),filter(date,date[date] <=maxx(date,date[date])))

Cumm Sales = CALCULATE(SUM(Sales[Purchase Date]),filter(allselected(date),date[date] <=maxx(date,date[date])))

Re: "Sales Running Total"... start date not being adhered to

michellepace — Wed, 02 Sep 2020 16:48:26 GMT

Hi amitchandak , thank you for your reply. I have that solution too. However, I am trying to learn dax. And I am trying to figure out how one could modify the equation given by DAX PATTERNS above such that the cumulative total begins at the minimum selected dated. Any ideas or is ti completely not possible?

Re: "Sales Running Total"... start date not being adhered to

dedelman_clng — Wed, 02 Sep 2020 17:13:24 GMT

michellepace - just a suggestion, but the pages of the DAX Patterns have a comment section and the authors actively respond to questions specifically for their patterns.

Hope this helps,

David

Re: "Sales Running Total"... start date not being adhered to

Greg_Deckler — Wed, 02 Sep 2020 17:14:32 GMT

michellepace - The standard Running Total quick measure works great. See attached PBIX, Table (13), Page 13.

SALES running total in DATE = CALCULATE( SUM('Table (13)'[SALES]), FILTER( ALLSELECTED('Table (13)'[DATE]), ISONORAFTER('Table (13)'[DATE], MAX('Table (13)'[DATE]), DESC) ) )

Re: "Sales Running Total"... start date not being adhered to

Greg_Deckler — Wed, 02 Sep 2020 18:59:41 GMT

michellepace - Also works with separate Calendar table:

SALES running total in Date 2 = CALCULATE( SUM('Table (13)'[SALES]), FILTER( ALLSELECTED('Calendar'[Date]), ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC) ) )

Re: "Sales Running Total"... start date not being adhered to

michellepace — Mon, 14 Sep 2020 03:40:30 GMT

Thank you Greg.