Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
4 years ago
Solved

Running Total until Today

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.