Non-empty running total

[Anonymous]

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.

Any clue how I could enhance this measure ?

[amitchandak · Accepted solution]

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

[Anonymous]

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:

Am I missing something ?

[amitchandak]

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

[Anonymous]

Sure @amitchandak , should have done it earlier, apologies

https://wetransfer.com/downloads/4e2d5fe409cc9dde5bc7e9b43829afa420210617144950/6209e867cbc01a305370...