Forum Discussion
Group by Monthly Growth Rate
Hi,
I got the following sample data:
| Product Group | Product | Monthly Start Date | Sales Qty |
| Mobile Phone A | Mobile Phone A-1 | 1/1/2021 | 100 |
| Mobile Phone A | Mobile Phone A-1 | 2/1/2021 | 120 |
| Mobile Phone B | Mobile Phone B-1 | 1/1/2021 | 90 |
| Mobile Phone B | Mobile Phone B-1 | 2/1/2021 | 78 |
What I want is to calculate the weekly product growth rate (below).
| Product Group | Product | Month Start Date | Growth Rate |
| Mobile Phone A | Mobile Phone A-1 | 1/1/2021 | null |
| Mobile Phone A | Mobile Phone A-1 | 2/1/2021 | 20% |
| Mobile Phone B | Mobile Phone B-1 | 1/1/2021 | null |
| Mobile Phone B | Mobile Phone B-1 | 2/1/2021 | -13% |
I guess I need to use groupby and sort order by the Month Start Date and calculate the rate.
The ultimate goal is to calculate the average weekly growth rate:
| Product Group | Product | Average Weekly Growth Rate |
| Mobile Phone A | Mobile Phone A-1 | |
| Mobile Phone B | Mobile Phone B-1 |
Does anyone know the best way of calculating it?
Thanks.
Hi, zakkyang
Try formulas as below:
pre_Sales Qty = VAR current_product = MAX ( 'Table'[Product] ) VAR pre_month = DATEADD ( 'Table'[Monthly Start Date], -1, MONTH ) VAR result = CALCULATE ( MAX ( 'Table'[Sales Qty] ), FILTER ( ALL ( 'Table' ), 'Table'[Product] = current_product && 'Table'[Monthly Start Date] = pre_month ) ) RETURN resultGrowth Rate = DIVIDE ( SUM ( 'Table'[Sales Qty] ) - [pre_Sales Qty], [pre_Sales Qty] )Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- AllisonKennedy
Community Champion
zakkyang You will likely need an Index for this. Once you have an index you can either try to do this calculation in Power Query, or use variables in DAX:
https://www.myonlinetraininghub.com/referencing-next-row-power-query
- v-easonf-msft
Community Support
Hi, zakkyang
Try formulas as below:
pre_Sales Qty = VAR current_product = MAX ( 'Table'[Product] ) VAR pre_month = DATEADD ( 'Table'[Monthly Start Date], -1, MONTH ) VAR result = CALCULATE ( MAX ( 'Table'[Sales Qty] ), FILTER ( ALL ( 'Table' ), 'Table'[Product] = current_product && 'Table'[Monthly Start Date] = pre_month ) ) RETURN resultGrowth Rate = DIVIDE ( SUM ( 'Table'[Sales Qty] ) - [pre_Sales Qty], [pre_Sales Qty] )Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- AllisonKennedy
zakkyang Great that this solution is working for you, just be careful that it's doing EXACTLY what you want....
It is returning the MAX qty value from the previous month, which might not be the last date of the previous month - do you only have one record per month for each row?
Also be careful using
MAX ( 'Table'[Product] )in that first line of code - it will return the last product alphabetically so may give funny results if there is more than one product selected.
