Forum Discussion
QoQ Growth Rolling Avg. % Query
I have calculated Avg. Rolling Quarter sales for current year with the help of Quick Measure, but while calculating Q0Q Growth Avg. Rolling %, PBI is Picking Current year sales instead of Avg. Rollling sales to calculate % Growth.
| RESULT I WANT | PBI RESULTS I AM GETTING | ||||||
| 2025 | Calculation Remarks | Calculation Remarks | Calculation Remarks | ||||
| Month | Sum of Value | Avg.Rolling Qtr | Avg. Rolling QoQ% | Avg. Rolling Quarter | Avg. Rolling QoQ% | PBI Avg Rolling QoQ% | |
| Jan | 3,000,000 | 3,000,000 | 0.00% | B4 | 0 | 0.00% | |
| Feb | 3,200,000 | 3,100,000 | 0.00% | B4+B5/2 | 0 | 0.00% | |
| Mar | 3,500,000 | 3,233,333 | 0.00% | B4+B5+B6/3 | 0 | 0.00% | |
| Apr | 3,860,000 | 3,520,000 | 17.33% | B5+A6+B7/3 | C7/C4*100 | 17.33% | C7/B4*100 |
| May | 4,000,000 | 3,786,667 | 22.15% | B6+B7+B8/3 | C8/C5*100 | 18.33% | C8/B5*100 |
| Jun | 4,200,000 | 4,020,000 | 24.33% | B7+B8+B9/3 | C9/C6*100 | 14.86% | C8/B6*100 |
| 1) | Sales 2025 = | |||||||||||||||||
| CALCULATE( | ||||||||||||||||||
| SUM('MSD Sales Data'[Value]), | ||||||||||||||||||
| FILTER( | ||||||||||||||||||
| 'MSD Sales Header Data', | ||||||||||||||||||
| YEAR('MSD Sales Header Data'[MonthYear].[Date]) = YEAR(TODAY()) | ||||||||||||||||||
| ) | ||||||||||||||||||
| ) | ||||||||||||||||||
| 2) Quick Measure | Rolling Avg.2025 = | |||||||||||||||||
| IF( | ||||||||||||||||||
| ISFILTERED('MSD Sales Header Data'[MonthYear]), | ||||||||||||||||||
| ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), | ||||||||||||||||||
| VAR __LAST_DATE = ENDOFMONTH('MSD Sales Header Data'[MonthYear].[Date]) | ||||||||||||||||||
| VAR __DATE_PERIOD = | ||||||||||||||||||
| DATESBETWEEN( | ||||||||||||||||||
| 'MSD Sales Header Data'[MonthYear].[Date], | ||||||||||||||||||
| STARTOFMONTH(DATEADD(__LAST_DATE, -2, MONTH)), | ||||||||||||||||||
| __LAST_DATE | ||||||||||||||||||
| ) | ||||||||||||||||||
| RETURN | ||||||||||||||||||
| AVERAGEX( | ||||||||||||||||||
| CALCULATETABLE( | ||||||||||||||||||
| SUMMARIZE( | ||||||||||||||||||
| VALUES('MSD Sales Header Data'), | ||||||||||||||||||
| 'MSD Sales Header Data'[MonthYear].[Year], | ||||||||||||||||||
| 'MSD Sales Header Data'[MonthYear].[QuarterNo], | ||||||||||||||||||
| 'MSD Sales Header Data'[MonthYear].[Quarter], | ||||||||||||||||||
| 'MSD Sales Header Data'[MonthYear].[MonthNo], | ||||||||||||||||||
| 'MSD Sales Header Data'[MonthYear].[Month] | ||||||||||||||||||
| ), | ||||||||||||||||||
| __DATE_PERIOD | ||||||||||||||||||
| ), | ||||||||||||||||||
| CALCULATE( | ||||||||||||||||||
| [Sales 2025], | ||||||||||||||||||
| ALL('MSD Sales Header Data'[MonthYear].[Day]) | ||||||||||||||||||
| ) | ||||||||||||||||||
| ) | ||||||||||||||||||
| ) | ||||||||||||||||||
| 3) | QoQ Growth (Rolling Avg 3M) % = | |||||||||||||||||
| VAR CurrentValue = | ||||||||||||||||||
| [Rolling Avg.2025] | ||||||||||||||||||
| VAR PreviousQuarterValue = | ||||||||||||||||||
| CALCULATE( | ||||||||||||||||||
| [Rolling Avg.2025], | ||||||||||||||||||
| DATEADD('MSD Sales Header Data'[MonthYear].[Date], -1, QUARTER) | ||||||||||||||||||
| ) | ||||||||||||||||||
| RETURN | ||||||||||||||||||
| DIVIDE(CurrentValue - PreviousQuarterValue,PreviousQuarterValue) |
Hello AartiD,
Thank you for reaching out to the Microsoft Fabric Forum Community.
I’ve reproduced your requirement in Power BI using the sample data and DAX logic you've described.
I can confirm that it is possible to achieve the expected Rolling Avg. and QoQ Growth % output exactly as per your Excel logic including showing 0% for Jan, Feb, and Mar, and calculating QoQ % only from April onward.
To address your specific issue:
- The default Power BI DATEADD()-based approach does not align with the custom rolling logic you need.
- I implemented a custom DAX measure to calculate the 3-month rolling average and another measure that returns QoQ % only when a full prior 3-month period exists just like your Excel example.
For your reference, I’m attaching a .pbix file.
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Harshitha.
