Month To Date Help

[webbmd92]

I am trying to get the Goal MTD to display "Today's" value relative to the month end goal.

The issue I am coming accross is that when I change to a previous month, GOAL MTD still shows "Today's" value.

What I am trying to accomplish is that if Today is 4/16/2025, and I also want to look at March, I want to be able to see 3/16/2025 value when changing the slicer to March.

1. GOAL MTD

Today's ME Shipment Goal = Calculate(SUM('Sales Targets'[Goal]), 'Calendar - Transaction Date'[Transaction Date] = TODAY())

2. Month End Goal

Bookings Goal MTD = VAR Max_Date = MAX('Bookings Target'[Date])

VAR Latest_Goal = Calculate(SUM('Bookings Target'[Goal]), ('Bookings Target'[Date])= Max_Date)

Return Latest_Goal

[v-mdharahman · Accepted solution]

Hi @webbmd92,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are faing issue with your PowerBi dashboard that compares Month-to-Date values of bookings and shipments against their respective goals.

You're encountering this issue because your current GOAL MTD measure uses the TODAY() function, which always evaluates to the current system date (e.g., 4/16/2025). This means that even when you select a different month, lets say May or June, the MTD goal calculation is still pulling values for April 16 which causes a mismatch when viewing historical months.

To make the GOAL MTD dynamically reflect the same day number relative to the selected month, you'll want to replace TODAY() with a logic that calculates the nth day of the selected month, based on today’s day.

You can try this DAX measure and check if it solves your issue.

Goal MTD Dynamic =
VAR SelectedDate = MAX('Calendar - Transaction Date'[Transaction Date])
VAR DayOfMonth = DAY(TODAY())

VAR StartOfMonth = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1)
VAR EndOfPeriod =
MIN(
EOMONTH(SelectedDate, 0),
DATE(YEAR(SelectedDate), MONTH(SelectedDate), DayOfMonth)
)

RETURN
CALCULATE(
SUM('Sales Targets'[Goal]),
'Calendar - Transaction Date'[Transaction Date] >= StartOfMonth &&
'Calendar - Transaction Date'[Transaction Date] <= EndOfPeriod
)

This measure will dynamically compute the date range from the 1st of the selected month up to the same “day number” as today, and calculates the MTD goal accordingly.

I would also take a moment to thank @amitchandak, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman · Accepted solution]

Hi @webbmd92,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are faing issue with your PowerBi dashboard that compares Month-to-Date values of bookings and shipments against their respective goals.

You're encountering this issue because your current GOAL MTD measure uses the TODAY() function, which always evaluates to the current system date (e.g., 4/16/2025). This means that even when you select a different month, lets say May or June, the MTD goal calculation is still pulling values for April 16 which causes a mismatch when viewing historical months.

To make the GOAL MTD dynamically reflect the same day number relative to the selected month, you'll want to replace TODAY() with a logic that calculates the nth day of the selected month, based on today’s day.

You can try this DAX measure and check if it solves your issue.

Goal MTD Dynamic =
VAR SelectedDate = MAX('Calendar - Transaction Date'[Transaction Date])
VAR DayOfMonth = DAY(TODAY())

VAR StartOfMonth = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1)
VAR EndOfPeriod =
MIN(
EOMONTH(SelectedDate, 0),
DATE(YEAR(SelectedDate), MONTH(SelectedDate), DayOfMonth)
)

RETURN
CALCULATE(
SUM('Sales Targets'[Goal]),
'Calendar - Transaction Date'[Transaction Date] >= StartOfMonth &&
'Calendar - Transaction Date'[Transaction Date] <= EndOfPeriod
)

This measure will dynamically compute the date range from the 1st of the selected month up to the same “day number” as today, and calculates the MTD goal accordingly.

I would also take a moment to thank @amitchandak, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[webbmd92 · Accepted solution]

Thank you for your reply, I was able to figure it out creating a Binary IF statement when changing the Month(Date) Slicer.

Step 1:

CurrentMonth =

IF (

    MONTH(TODAY()) = MAX('Calendar - Transaction Date'[Month]) && YEAR(TODAY()) = MAX('Calendar - Transaction Date'[Transaction Date Year Number]),

    1,

    0

)

Step 2:

PreviousMonths =

IF (

    MONTH(TODAY()) > MAX('Calendar - Transaction Date'[Month]) && YEAR(TODAY()) = MAX('Calendar - Transaction Date'[Transaction Date Year Number]) ||

    YEAR(TODAY()) > MAX('Calendar - Transaction Date'[Transaction Date Year Number]),

    1,

    0

)

Step 3:

MTD BOOKING GOAL 2 =

IF (

    [CurrentMonth]=1,

    [Today's ME Booking Goal],

    IF(

        [PreviousMonths] = 1,

        [MonthEnd Bookings Goal],

        BLANK()

        ))

So if I am in current month I get "Today's" value, which is MTD(How the underlying data is setup), and if I am in a previous month, I get the total for that month.

[amitchandak]

@webbmd92 , Please use date table, joined with date of your table and then you can measures like

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Max date of table- replace with your table

MTD =
var _max = MaxX(allselected('Table'),'Table' [Date])
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

You can also use TI function

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[webbmd92]

Thank you for the reply. Unfortunately, neither DAX measure works. It is giving me the total sum for the whole columns.

Attached is the MTD Goal Table.

MTD GOAL 2 =

VAR _MAX = IF(ISFILTERED('Calendar - Transaction Date'),MAX('Calendar - Transaction Date'[Transaction Date]), TODAY())

VAR _MIN = EOMONTH(_MAX,-1) +1

RETURN

CALCULATE([Booking Goal],DATESBETWEEN('Calendar - Transaction Date'[Transaction Date],_MIN,_MAX))

[webbmd92]

I think a better way to describe what I am trying to accomplish is if I am in current month I want today's value, if I am looking at a previous month I want to see MTD(which is essentially the entire month) or entire month summed.