MTD total showing Sum for the year instead

[KW123]

Hi, 

I have a Dates table as well as a separate Goals table. The Goals table contains a list of the 12 months, and a column with each monthly goal.  

I need a MTD column in my report that will show the MTD goals added together for the sliced months.  So if I have it sliced on May for example, it will show the goal total for Jan-May.  

MTD Goal =

TOTALMTD(SUM('2022 Goals'[Goal]),Dates[Date])

However on the report, the Goal comes out as the total for the entire year, no matter which month is sliced.  Is there a way to fix this? 

Thanks, 

[v-yanjiang-msft · Accepted solution]

Hi @KW123 ,

According to your description, I create a sample.

2022 Goals table:

Dates table:

The two tables are related with the Month column.

In your formula, there're two errors, first is the ALLSELECTED function, it only takes the values be selected in the slicer into consideration, replace it with ALL function. Then filter the all date table, not only the date column. Modify the formula to:

Measure =
CALCULATE (
    SUM ( '2022 Goals'[Goal] ),
    FILTER (
        ALL ( 'Dates' ),
        ISONORAFTER ( 'Dates'[Date], MAX ( 'Dates'[Date] ), DESC )
    )
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KW123]

I've tried it as a running total calculation too, and it just adds all 12 months of goals together instead of just up until the sliced month

CALCULATE(

    SUM('2022 Goals'[Goal]),

    FILTER(

        ALLSELECTED('Dates'[Date]),

        ISONORAFTER('Dates'[Date], MAX('Dates'[Date]), DESC)

    )

)

[KW123]

Thanks @amitchandak 
Neither of those DAX work for what I am trying to acheive.  It is still showing each row as the YTD goal total rather than up to the current month. 

[amitchandak]

@KW123 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[amitchandak]

@KW123 , First of MTD is only needed when you show data date-wise month onward you can use

SUM('2022 Goals'[Goal])

or YTD

YTD Goal =
TOTALYTD(SUM('2022 Goals'[Goal]),Dates[Date])

Or change grand total

if(isinscope(Table[Visual Column]),
TOTALMTD(SUM('2022 Goals'[Goal]),Dates[Date]) , SUM('2022 Goals'[Goal]))

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/