YTD works as expected but MTD and QTD do not

[devslbig]

Strange issue - YTD calcs / table work exactly as expected, but QTD and MTD do not. They return SUMs and tables with information that does not fall into the DATEs and ignores data which should be included. Any help is appreciated and thanks in advance

[albertospelta]

@devslbig Could you share a minimal .pbix file with some dummy data that reproduces this issue?

[marcosqlbi]

@devslbig The notion of MTD/QTD/YTD is always compared to the current filter context. See the descriptions here: https://www.daxpatterns.com/standard-time-related-calculations/
If you want a MTD/QTD/YTD for the last month/quarter/year available, you should create a custom template.

[marcosqlbi]

As I also described in a similar issue.

MTD/QTD/YTD are aggregation functions that work relatively to the current filter context.
If you do not include any date/group/filter in your report, then they consider the last date in the Date table, not the last date with data.
If you want to create a selection for the last date with data, create an additional calculated column, such as

CurrentDate =
VAR LastDateWithData = CALCULATE ( MAX ( 'Date'[Date] ), 'Date'[DateWithTransactions] = TRUE, REMOVEFILTERS() )
RETURN IF ( LastDateWithData == 'Date'[Date], "LastDayWithData" )

Then, in the report create a filter where CurrentDate is LastDayWithData in the filter pane, at the report or page level, depending on how you want to structure your report. At that point, the measures in the template will behave as you expect without any date selected.

Please, download the sample files of the patterns on https://www.daxpatterns.com/ to see examples of how these functions work.