cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ad7
Helper I
Helper I

Non-empty running total

Hello everyone,

 

I found this DAX code to calculate a yearly running total :

 

 

 

Qty Invoiced running total in Month = 
CALCULATE(
	SUM('Revenue'[Qty Invoiced]),
	FILTER(
		ALLSELECTED('Revenue'),
		'Revenue'[Year] = MAX ( 'Revenue'[Year] ) &&
        'Revenue'[Month] <= MAX ( 'Revenue'[Month] )
	)
)

 

 

 

I am using the Area chart with the Year in legend to show the yearly difference however my running total stop on the month when I don't have data, to continue on the next one.

Capture.PNG

 

Any clue how I could enhance this measure ?

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Ad7  Use date table that will make sure you have data for the month you do not have data

 

Qty Invoiced running total in Month = 
CALCULATE(
	SUM('Revenue'[Qty Invoiced]),
	FILTER(
		ALLSELECTED('Date'),
		'Date'[Year] = MAX ( 'Date'[Year] ) &&
        'Date'[Month] <= MAX ( 'Date'[Month] )
	)
)

 

to means this seem like YTD only

YTD Sales = CALCULATE(SUM(Revenue[Qty Invoiced]),DATESYTD('Date'[Date],"12/31"))

 

for runnign total

Qty Invoiced running total in Month = 
CALCULATE(
	SUM('Revenue'[Qty Invoiced]),
	FILTER(
		ALLSELECTED('Date'),
		'Date'[DAte]  <= MAX ( 'Date'[Date] )
	)
)

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@Ad7  Use date table that will make sure you have data for the month you do not have data

 

Qty Invoiced running total in Month = 
CALCULATE(
	SUM('Revenue'[Qty Invoiced]),
	FILTER(
		ALLSELECTED('Date'),
		'Date'[Year] = MAX ( 'Date'[Year] ) &&
        'Date'[Month] <= MAX ( 'Date'[Month] )
	)
)

 

to means this seem like YTD only

YTD Sales = CALCULATE(SUM(Revenue[Qty Invoiced]),DATESYTD('Date'[Date],"12/31"))

 

for runnign total

Qty Invoiced running total in Month = 
CALCULATE(
	SUM('Revenue'[Qty Invoiced]),
	FILTER(
		ALLSELECTED('Date'),
		'Date'[DAte]  <= MAX ( 'Date'[Date] )
	)
)

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi Amit,

Thanks for the inputs.

I created a date table following this procedure , however whether I write one of the two codes you mentionned my output is incorrect:

Capture.PNG

Am I missing something ?

@Ad7 , hope axis is also coming from date table also check formula, seems like only allselected  and = max is mising

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors