topic Re: MTD total showing Sum for the year instead in DAX Commands and Tips

MTD total showing Sum for the year instead

KW123 — Thu, 21 Jul 2022 22:37:15 GMT

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,

Re: MTD total showing Sum for the year instead

amitchandak — Fri, 22 Jul 2022 03:19:46 GMT

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/

Re: MTD total showing Sum for the year instead

KW123 — Fri, 22 Jul 2022 14:39:30 GMT

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.

Re: MTD total showing Sum for the year instead

KW123 — Fri, 22 Jul 2022 14:46:51 GMT

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)

)

Re: MTD total showing Sum for the year instead

amitchandak — Fri, 22 Jul 2022 18:09:33 GMT

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

Re: MTD total showing Sum for the year instead

v-yanjiang-msft — Wed, 27 Jul 2022 07:06:08 GMT

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.