cancel
Showing results for
Did you mean:
Helper V

## MTD total showing Sum for the year instead

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,

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Community Support

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.

Helper V

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)
)
)
Helper V

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.

Super User

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

Super User

@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/

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors