Power BI — MTD Questions — Time Intelligence 3–5
In this blog, we will try to answer, 10 Most Frequently Asked Questions on Month Till Date (MTD) in Power BI.
Abbreviations:
MTD: Month Till Date
MQTD: Last Month Till Date
LMQTD : Last Year Same Month Till Date
Pre-requisites:
For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.)
Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
Start Of Year = STARTOFYEAR(‘Date’[Date],”12/31")
End of Year = ENDOFYEAR(‘Date’[Date],”12/31")
Day of Year = Datediff([Start Of Year],[Date],Day)+1
Year = YEAR(‘Date’[Date])Start Date of Month = STARTOFMONTH('Date'[Date])
End Date of Month = ENDOFMONTH('Date'[Date])
Day of Month = DATEDIFF('Date'[Start Date of Month],'Date'[Date],DAY)+1
Work Day = if(WEEKDAY('Date'[Date],2)<6,1,0)
Weekday Name = FORMAT('Date'[Date],"DDDD")
Month Rank = RANKX(all('Date'),'Date'[Start Date of Month],,ASC,Dense)Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)
- What are the various functions for MTD?
DATESMTD and TOTALMTD
MTD = calculate([Sales],datesmtd(‘Date’[Date]))
MTD -1 = TOTALMTD([Sales],’Date’[Date])- How to use these functions if we are not using the regular calendar months, we have custom periods ?
There is no other option, you have to use the Rank way as discussed later in this post for month too. You can create rank on period start date and use that to get this period and last period etc. Refer to this. - How to get previous MTD/ Last month till date?
LMTD = calculate([Sales],DATESMTD(DATEADD('Date'[Date],-1,MONTH)))
LMTD - 1 = TOTALMTD([Sales],DATEADD('Date'[Date],-1,MONTH))- What date MTD function will take, if we do not select the date?
End date of the Calendar will be considered as end date of MTD - How can we get working days of this month and last month till date?
Working Days This Month = CALCULATE(SUM('Date'[Work Day]),DATESMTD('Date'[Date]))Working Days Last Month = CALCULATE(SUM('Date'[Work Day]),DATESMTD(DATEADD('Date'[Date],-1,MONTH)))
- How to get complete last Month data?
LMTD Complete = calculate([Sales],DATESMTD(ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))))
Previous Month = CALCULATE([Sales],PREVIOUSMONTH('Date'[Date]))- How to get this Month vs last Month without using time intelligence?
(Need to use row context)
This Month = CALCULATE([Sales], FILTER(ALLSELECTED('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE([Sales], FILTER(ALLSELECTED('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))- How to get MTD and LMTD without using time intelligence?
This Month MTD = CALCULATE([Sales], FILTER(ALLSELECTED('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && 'Date'[Day of Month]<=MAX('Date'[Day of Month])) )Last Month MTD = CALCULATE([Sales], FILTER(ALLSELECTED('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) -1 && 'Date'[Day of Month]<=MAX('Date'[Day of Month])) )
- How can we get same Month last year?
Last year Same month = CALCULATE([Sales],DATESMTD(DATEADD('Date'[Date],-1,YEAR)) )- How can we get Rolling N months data?
Rolling 3 = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH('Date'[Date]),-3,MONTH)) Rolling 3 till Last Month = CALCULATE([Sales],DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)),-3,MONTH))
Few other important measures
Trailing Qtr = CALCULATE([Sales],DATEADD(‘Date’[Date],-1,QUARTER))
Trailing Year = CALCULATE([Sales],DATEADD(‘Date’[Date],-1,year))
Trailing 2 Years = CALCULATE([Sales],DATEADD(‘Date’[Date],-2,year))Let us know what you think about the above suggested methods.
Share the scenario where it helped you. Please find the file, for you to explore.
Also, would love to hear your approach on the alternate methods of using above Time Intelligence functions.
Want to Learn More, Explore these videos and video series.
If you want to learn Power BI from the ground up — covering everything from basic data modeling to advanced DAX and real-world analytics — be sure to explore my 20-hour Masterclass, packed with 150+ essential topics to elevate your data analytics and business intelligence skills.
Complete Power BI Tutorial/Course in Just 11 Hours
Mastering Microsoft Fabric: 30 Videos
Want to Learn SQL: 20 Videos
Mastering Power BI: 230+ Videos
Power BI Concepts- How Power BI Works- 150+ Videos
Migrating from Tableau to Power BI: 30 Videos
DAX for SQL Users: 10 Videos
What works in Power BI Direct Query- 10+ Videos
सीखे Power BI हिंदी में- 60+ Videos
Power BI Hindi On Demand- 10+ Videos
About the Author: Amit Chandak
Amit Chandak is Chief Analytics Officer at Kanerika Software Private Limited where the priority is to get the most value out of the customers’ data. He is also an avid Power BI Super User due to his contributions to the Power BI community.
Power BI is turning 5! In celebration of this achievement, Amit is sharing 5 blog posts on the community, Series: Power BI turns 5. You can find the links in this post.
If there is any other topic in your mind, do not hesitate to reach out to Amit. He is looking for different talking points to share his thoughts on Power BI.
You can follow his work on LinkedIn, Twitter, Medium, Youtube
