Forum Discussion
Custom QTD Calculations
I'm having difficulties with calculating Quarter to date measures due to the fact that the quarters are designated by very specific dates, therefore I can't just offshift by months or weeks to accomodate the whole date table.
I'm not sure if I should go about creating some sort of flag that gives me today's quarter and then simply filter by that (but then I would need to change the filter every time it changes) or what.
I can't use the QTD dax functions since those give me the basic calendar quarters which I don't want.
The way the data is setup is I have a list of each end of week and which custom quarter they belong to so I bring those into the date table through a lookupvalue.
Any help is appreciated.
5 Replies
GuillaumeB , Create Rank for then Quarters, then you Rank to get Qtr, Last Qtr QOQ
You need Qtr start date. Based on that you can calculate day of qtr and get QTD
I got it like this
Start of Year = STARTOFYEAR(Dates[Date],"1/31") // Start of year can take a start date Strat of Qtr = date(year(Dates[Start of Year]), month(Dates[Start of Year])+Dates[Add Qtr],1) Day of Qtr = DATEDIFF([Start of Q],[Date],Day)+1 Qtr No = "Q"& QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)+1 QTD = CALCULATE([Measure], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank]) && Dates[Day of Qtr ] <= Max(Dates[Day of Qtr ]))) Last QTD = CALCULATE([Measure], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank])-1 && Dates[Day of Qtr ] <= Max(Dates[Day of Qtr ])))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
amitchandak wrote:GuillaumeB, Create Rank for then Quarters, then you Rank to get Qtr, Last Qtr QOQ
You need Qtr start date. Based on that you can calculate day of qtr and get QTD
I got it like this
Start of Year = STARTOFYEAR(Dates[Date],"1/31") // Start of year can take a start date Strat of Qtr = date(year(Dates[Start of Year]), month(Dates[Start of Year])+Dates[Add Qtr],1) Day of Qtr = DATEDIFF([Start of Q],[Date],Day)+1 Qtr No = "Q"& QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)+1 QTD = CALCULATE([Measure], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank]) && Dates[Day of Qtr ] <= Max(Dates[Day of Qtr ]))) Last QTD = CALCULATE([Measure], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank])-1 && Dates[Day of Qtr ] <= Max(Dates[Day of Qtr ])))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.How does this:
Strat of Qtr = date(year(Dates[Start of Year]), month(Dates[Start of Year])+Dates[Add Qtr],1)Work exactly? What am I supposed to putt for "Dates[Add Qtr]"
Also will this be static? 2020 started on December 29th, 2019 so PBI needs to automatically detect that 2021 will start on December 30th, 2020.
GuillaumeB , missed columns
Add Qtr = QUOTIENT(DATEDIFF(Dates[Start of Year], Dates[Date],MONTH),3)*3 Qtr Rank = RANKX(ALL(Dates),Dates[Strat of Qtr],,ASC,Dense)
