Running Total until Today

[joshua1990]

Hi all!

I have a bar chart that shows me the forecast for the whole month, as a running total value.

Then I have the actual sales also shown in the same bar chart.

I use this Dax formula to get a running total for the sales:

Sales RT MTD = 
CALCULATE(
    SUMX(
        'Calendar',
        [Sales]
    ),
    FILTER(
        ALL( 'Calendar' ),
        'Calendar'[Year]
            = VALUES( 'Calendar'[Year] )
            && 'Calendar'[Month Num]
                = MAX( 'Calendar'[Month Num] )
            && 'Calendar'[Date]
                <= MAX( 'Calendar'[Date] )
    )
)

Unfortuantely this measure displays the sales for the full month and not just for that past. The running total sales rate as of today is displayed for the future.

How can I filter the measure above to get values until today?

Please note the is a second measure in the same bar chart that shows the forecast for the future. 

[Anonymous · Accepted solution]

Hi @joshua1990 ,

Please try below dax formula

Sales RT =
CALCULATE (
    SUM ( Sale[Sales] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && 'Date'[Date] <= TODAY ()
    )
)

My test table:

Date:

Sale:

Mode:

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@joshua1990 , Try like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date]) && 'Date'[date]<= today()  ))

or

Cumm Sales = if(max('Date'[Date]) <= Today() , CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date]))) , blank() )