MTD and YTD measures that work no matter the year/month

[Veblengood]

I have a dataset with transactional data (Df), which is connected to a date table. The date table goes beyond the dates the transactional data.

I have created MTD and YTD measures, which aims to compare dates up to the exact date of the last updated date in the transactional data, comparable with all previous years. I have thus created the following measures

YTD:

Sales YTD =

VAR

maxmonth=month(DATE(LEFT(max(Df[Dateint]),4),RIGHT(max(Df[Dateint]),2),1))

Return

CALCULATE([Sales], FILTER(ALL(Dates[Date].[MonthNo]),Dates[Date].[MonthNo] <= maxmonth),Dates[Ispastdays]=TRUE)

Where dateint is the date in integers, and ispastdays is:

VAR LastSaleDate = MAXX(FILTER(Df, Df[Aar]==MAX(Df[Aar])),Df[ISOday])

RETURN

'Dates'[ISOday] <= LastSaleDate

ISOday is day 1,2...365 etc. of the year.

A similar measure is created for MTD:

Sales MTD = var maxmonth =CALCULATE(MONTH(MAX(Df[Dato])),ALL(Df))

return

CALCULATE([Sales],FILTER(ALL(Df[Dato]),month(Df[Dato]) =maxmonth), 'Dates'[Ispastdays]=TRUE)

The measure result in that the ytd and mtd become comparable with the latest year. However, if I wish to compare MTD in a different month and YTD over previous years, this no longer becomes possible, as it is locked based on this years ISOdays. Creating the Ispastdays as part of the measure yields the same problem as with using timeintelligence measures: they yield full years and the december month for MTD.

Is it possible to create MTD and YTD measures which are more dynamic, comparable through any year (YTD yields a result beyond the latest date in the transactional dataset) and months (MTD yields a result for other months as well beyond the latest month)?

[amitchandak]

@Veblengood , if you have date you should use time intelligence with date table

example

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

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA