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:

Pre-requisites:

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

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.

About the Author: Amit Chandak

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

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