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:
QTD: Quarter Till Date
LQTD: Last Quarter Till Date
LYQTD : Last Year Same Quarter 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 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.
Want to Learn More, Explore these videos and video series.
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
