MTD function not giving full set of dates

[manishpbi001]

hi, i have a date table that has column date starting jan 1, 2024 till dec 31 2025. i am trying to create another table in which i want MTD dates. So if today is Apr 3. My MTD table should have 3 dates. Apr1, 2 and 3. Here is my dax expression for the table 

Date Periods = ADDCOLUMNS(DATESMTD(DateTable[Date]),"Type","MTD")

But this table is showing me 31 rows, Dec 1 , 2025 to Dec 31, 2025. 

what am i doing wrong? this looks simple. i can get it to work using datebetween etc. but datebetween looks like a very round about way of doing it. i want to extend it to make mtd, ytd, etc.

[rohit1991]

Hi @manishpbi001

The issue you're encountering with the DATESMTD function returning dates from December 2025 instead of the current month is likely due to the context in which the DAX expression is evaluated it’s using the maximum date in your DateTable[Date], which is December 31, 2025, rather than today's date. Since DATESMTD works based on the current filter context, and your table expression doesn't explicitly limit it to today's date, it defaults to the max.

Could you please try below measure:

Date Periods = 
ADDCOLUMNS(
    DATESMTD(FILTER(DateTable, DateTable[Date] <= TODAY())),
    "Type", "MTD"
)

This ensures that the MTD range is correctly calculated based on the current date and filtered accordingly. Once this works, you can extend similar logic to build YTD, QTD, etc., consistently across your model.

[v-sgandrathi]

Hi @manishpbi001,

Thank you community members for the instant response.

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[v-sgandrathi]

Hi @manishpbi001,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @manishpbi001,

As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.

Thank you for your cooperation. Have a great day.

[SandeepMarwal]

it seems like MTD function works correctly if explicit filter context is there, otherwise it is providing incorrect dates.

for example, following measure provide table with correct dates

Newtable = CALCULATETABLE(DATESMTD('date'[Date]),'date'[Date]=TODAY())

but if I remove filter context, it provide incorrect dates as you describe.

Newtable = CALCULATETABLE(DATESMTD('date'[Date]))

[some_bih]

Hi @manishpbi001 MTD is time intelligence function and perform context transition.

Try code below
Current_Month_Dates =
FILTER (
'DateTable',
YEAR ( 'DateTable'[Date] ) = YEAR ( TODAY() )
&& MONTH ( 'DateTable'[Date] ) = MONTH ( TODAY() )
)
Please note that DateTable should be created as shown on link as best practice.

[kushanNa · Accepted solution]

HI @manishpbi001 

Try this table 

MTD_Table = 
FILTER(
    DateTable, 
    DateTable[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) &&
    DateTable[Date] <= TODAY()
)