Forum Discussion
Advocate ICalculating Quarter over Quarter Change
Hello,
I have a semantic model with a date dimension table. I also have a fact table from wherw I am calculating a measure called Volume, which is the equivelant of Count (Order ID).
I have a requirement to calculate the relative period change in the Absolute, and the Percent. The lowest interval must be the Day so in essence, the relative period change must be able to calculate incomplete periods as well.
My issue is the following -
When I use built-in functions such as PREVIOUSQUARTER, it compares the full calendar period of the previous sequential quarter against the current. So for example, if the current quarter range is July 1-7, it will compare the value for July 1-7 against the full Q2 which is 4/1-6/30. I need the comparison to follow a Quarter-Month-Day hierarchy as July 1-7 should be compared against 4/1 - 4/7 only.
My problems begin because quarters of the year can have different lengths, and end with different dates. For example, Q2 ends in 6/30. I tried using EDATE (MinDate, -3) and EDATE(MaxDate, -3) to identify the comparitive date ranges for the previous quarter in order to calculate my relative quarter over quarter change. However, I noticed that because the quarters end with different dates as in the case of Q2, 6/30 would never directly reference 3/31 as 6/31 does not exist.
I want to create a measure where the Volume for missing dates for the previous periods are aggregated in the Volume of the last day of the current period, but only if the last day of the current period is a true end of the quarter. So for example, 6/7 volume should only reference 3/7s volume, but 6/30s volume should reference 3/30 and 3/31.
Any suggestions would be greatly appreciated!
Hi ssrinath ,
Really solid breakdown — and you're absolutely right: built-in functions like PREVIOUSQUARTER() aren't designed for partial period comparisons, especially when you're working at the day level within a quarter.
Here’s a pattern that might help:
Step-by-step approach:
- Create a calculated column in your Date table to shift each date back by 3 months:
PreviousQuarterDate = EDATE('Date'[Date], -3)- Create a measure to get the Volume for the shifted date:
Volume_PrevQ = CALCULATE( [Volume], FILTER( ALL('Date'), 'Date'[Date] = EDATE(SELECTEDVALUE('Date'[Date]), -3) ) )This will give you a day-to-day aligned comparison between the current quarter and the same relative day in the previous quarter.
- Handle quarter-end edge cases\ For the special case where the current date is the last day of the quarter, and the previous quarter had fewer days (e.g., June 30 vs. March 31), you can add logic like:
IsQuarterEnd = 'Date'[Date] = CALCULATE(MAX('Date'[Date]), ALLEXCEPT('Date', 'Date'[Quarter], 'Date'[Year])) AdjustedVolume_PrevQ = IF( [IsQuarterEnd], CALCULATE( [Volume], DATESINPERIOD( 'Date'[Date], EDATE(SELECTEDVALUE('Date'[Date]), -3), -1, DAY ) ), [Volume_PrevQ] )This way, you can aggregate the last few days of the previous quarter only when the current date is the true quarter-end.
Let me know if you want help adapting this to your model — especially if you're using a custom calendar or fiscal quarters.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
Translation and text formatting supported by AI assistance
5 Replies
- burakkaragoz
Super User
Hi ssrinath ,
Really solid breakdown — and you're absolutely right: built-in functions like PREVIOUSQUARTER() aren't designed for partial period comparisons, especially when you're working at the day level within a quarter.
Here’s a pattern that might help:
Step-by-step approach:
- Create a calculated column in your Date table to shift each date back by 3 months:
PreviousQuarterDate = EDATE('Date'[Date], -3)- Create a measure to get the Volume for the shifted date:
Volume_PrevQ = CALCULATE( [Volume], FILTER( ALL('Date'), 'Date'[Date] = EDATE(SELECTEDVALUE('Date'[Date]), -3) ) )This will give you a day-to-day aligned comparison between the current quarter and the same relative day in the previous quarter.
- Handle quarter-end edge cases\ For the special case where the current date is the last day of the quarter, and the previous quarter had fewer days (e.g., June 30 vs. March 31), you can add logic like:
IsQuarterEnd = 'Date'[Date] = CALCULATE(MAX('Date'[Date]), ALLEXCEPT('Date', 'Date'[Quarter], 'Date'[Year])) AdjustedVolume_PrevQ = IF( [IsQuarterEnd], CALCULATE( [Volume], DATESINPERIOD( 'Date'[Date], EDATE(SELECTEDVALUE('Date'[Date]), -3), -1, DAY ) ), [Volume_PrevQ] )This way, you can aggregate the last few days of the previous quarter only when the current date is the true quarter-end.
Let me know if you want help adapting this to your model — especially if you're using a custom calendar or fiscal quarters.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
Translation and text formatting supported by AI assistance
ssrinathThanks so much for the detailed breakdown!
I should have also mentioned, my date dimension table is limited to the range of my fact table (Fact table min date is 12/01/2021 and max date is 7/02/2025). So when I tried to use the IsQuarterEnd measure as outlined, it returned 7/02/2025.
IsQuarterEnd = 'Date'[Date] = CALCULATE(MAX('Date'[Date]), ALLEXCEPT('Date', 'Date'[Quarter], 'Date'[Year]))Is there any way I can force this to identify that 7/2 is not the real end of the quarter as it should be 9/30 without importing the full 2025 from the date dimension table?
- Greg_Deckler
Community Champion
ssrinath You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008- ssrinath
Thank you so much for this - will check them out.
Hi ssrinath
Since you accepted the solution, I'm glad your issue has been resolved. If you have any further questions or encounter other issues, please don't hesitate to start a new thread in the community.Thank you for reaching out to the Microsoft Fabric Forum Community.
