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.

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)
DATESQTD and TOTALQTD
QTD = calculate([Sales],datesqtd(‘Date’[Date]))
QTD -1 = TOTALQTD([Sales],’Date’[Date])
  • 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))
  • 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 ))
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 )
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.
(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))
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])) )
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))

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