Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

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

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



Sales YTD =
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])
'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))
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)?


Super User
Super User

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




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:


Power BI — Year on Year with or Without Time Intelligence


Power BI — Month on Month with or Without Time Intelligence

Helpful resources


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.