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('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 ?

 

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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    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.