Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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('Rev...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
amitchandak
Super User
5 years ago

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.