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.

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")
)
DATESMTD and TOTALMTD
MTD = calculate([Sales],datesmtd(‘Date’[Date]))
MTD -1 = TOTALMTD([Sales],’Date’[Date])
  • 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))
  • 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)))
LMTD Complete = calculate([Sales],DATESMTD(ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))))
Previous Month = CALCULATE([Sales],PREVIOUSMONTH('Date'[Date]))
(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))
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]))  )
Last year Same month = CALCULATE([Sales],DATESMTD(DATEADD('Date'[Date],-1,YEAR)) )
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))

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.

Amit has 18+ years of experience in Business Intelligence, Analytics, Data Science. He is Chief Analytics Officer at Kanerika & Power BI community Super User