Power BI — QTD Questions — Time Intelligence 2–5

In this blog, we will try to answer, 10 Most Frequently Asked Questions on Quarter Till Date (QTD) 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 Of Qtr = STARTOFQUARTER(‘Date’[Date])
End Of Qtr = ENDOFQUARTER(‘Date’[Date])
Qtr No = FORMAT([Date],”Q”)
Qtr = FORMAT([Date],”YYYY”) & “-Q” & FORMAT([Date],”Q”)
Day of Qtr = DATEDIFF(‘Date’[Start Of Qtr],[Date],DAY)+1
Qtr Rank = RANKX(ALL(‘Date’),’Date’[Start Of Qtr],,ASC,Dense)
  • What are the various functions for QTD?
DATESQTD and TOTALQTD
QTD = calculate([Sales],datesqtd(‘Date’[Date]))
QTD -1 = TOTALQTD([Sales],’Date’[Date])
  • How to use these functions if we are not using the regular calendar year?
    You can give end date in both DATESQTD and TOTALQTD
    There is no way. You have to use the Rank way, discussed below in “
    without using time intelligence” questions. There is an idea created on idea.powerbi.com by me, please vote.
  • How to get previous QTD/ Last quarter till date?
LQTD = calculate([Sales],DATESQTD(DATEADD(‘Date’[Date],-1,QUARTER)))
LQTD — 1 = TOTALQTD([Sales],DATEADD(‘Date’[Date],-1,QUARTER))
  • What date QTD function will take, if we do not select the date?
    End date of the Calendar will be considered as the end date of QTD.
  • How to control QTD and LQTD till today and last Quarter same date, when slicer is not filtering the date?
QTD Today = var _max = TODAY() 
return calculate([Sales],datesQtd(‘Date’[Date]),filter(all(‘Date’),’Date’[Date] <=_max))
LQTD Today = var _max = date(year(today()),month(today())-3,day(today()))
return calculate([Sales],DATESQTD(DATEADD(‘Date’[Date],-1,QUARTER)),filter(ALLSELECTED(‘Date’),’Date’[Date] <=_max ))
  • How to control QTD and QTD till max date of fact this Quarter and last Quarter same date, when slicer is not filtering the date?
QTD Max Date = var _max = maxx(ALLSELECTED(Sales),Sales[Sales Date]) 
return
calculate([Sales],datesQtd(‘Date’[Date]) ,filter(‘Date’,’Date’[Date] <=_max ))
LQTD Max Date = var _max1 = maxx(ALLSELECTED(Sales),Sales[Sales Date])
var _max = date(year(_max1),month(_max1)-3,day(_max1))
return
calculate([Sales],DATESQTD(DATEADD(‘Date’[Date],-1,QUARTER)) ,’Date’[Date] <=_max )

In above two questions, quarter should be current quarter .

  • How to get complete last Quarter data?
LQTD Complete = calculate([Sales],datesqtd(ENDOFQUARTER(DATEADD(‘Date’[Date],-1,YEAR))))Previous Qtr = CALCULATE([Sales],PREVIOUSQUARTER(‘Date’[Date]))
These will complete last quarter as Grand total, even if you use month year. So better only use with Quarter as group by or no Time group by.
  • How to get this Quarter vs last Quarter without using time intelligence?
(Need to use row context)
This Quarter = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Qtr Rank]=max(‘Date’[Qtr Rank])))

Last Quarter = CALCULATE([Sales], FILTER(ALL(‘Date’),’Date’[Qtr Rank]=max(‘Date’[Qtr Rank])-1))
  • How to get QTD and LQTD without using time intelligence?
This Qtr QTD = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Qtr Rank]=max(‘Date’[Qtr Rank]) && ‘Date’[Day of Qtr]<=MAX(‘Date’[Day of Qtr])) )Last Qtr QTD = CALCULATE([Sales], FILTER(ALLSELECTED(‘Date’),’Date’[Qtr Rank]=max(‘Date’[Qtr Rank]) -1 && ‘Date’[Day of Qtr]<=MAX(‘Date’[Day of Qtr])) )
  • How can we get same quarter last year?
LYQTD = calculate([Sales],DATESQTD(DATEADD(‘Date’[Date],-1,YEAR)))

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store