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.
13 Replies
This can be done a bit easier with the WINDOW function
Another approach is to use a Visual Calculation, but those have limitation, like they only exist in the context of the visual in which they are created. But, they can be very powerful.
Hi,
Your expected result is not clear. Share the download link of the Excel file with your Excel formulas. I will try to convert those formulas into measures.
That takes me to a sign-in page.
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.Hello AartiD.,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Harshitha.Hi AartiD,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.
Regards,
Harshitha.
