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)?
