cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Veblengood
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)?
 
 
 
 
 
 

 

1 REPLY 1
amitchandak
Super User
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
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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.