cancel
Showing results 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

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

Super User

@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

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Announcements

#### 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.

#### 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!

#### Business Application LATAM Summit 2023

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

#### 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.

Top Solution Authors
Top Kudoed Authors