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

[michellepace]

 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

[amitchandak]

@michellepace , try like

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

or

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

[michellepace]

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? 

[Greg_Deckler · Accepted solution]

@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)
	)
)

[Greg_Deckler]

@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)
	)
)

[michellepace]

Thank you Greg.

[dedelman_clng]

@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

[Greg_Deckler]

@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.

[michellepace]

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