Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello all,
I want to create a report that shows sales growth up and down for each item compare with average of last 90 days or 3 months from that month. show me growth rate by %.
Below is the example I want to achieve in the powerbi report. Automatically do the math and give %.
Item_No | January | FEB | MARCH | APRIL | MAY |
Apple | 500 | 600 | 300 | 200 | 250 |
Calculations | (600-500)/500 =20% | 1. (500+600)/2 =550 2. (300-550)/550 =-45% | 1. (500+600+300)/3 = 466.7 2.(200-466.7)/466.7 = -57% | 1. (600+300+200)/3=275 2. (250-275)/275= -9% | |
Sales Growth | 20% | -45% | -57% | -9% |
1. Is it possible to days?
2. is there any another way to achieve this result?
Thank you,
Paul
Solved! Go to Solution.
Hi @Atinder ,
You can try the following methods.
Cumulative 3 month =
Var N1=SUMMARIZE(FILTER(ALL('Table'),[Date]<SELECTEDVALUE('Table'[Date])),[Date],"Sum",SUM('Table'[Sales]))
Var N2=TOPN(3,N1,[Date],DESC)
Var Cumulative3month=SUMX(N2,[Sum])
return
Cumulative3month
Count =
Var N1=CALCULATE(COUNT('Table'[Date]),FILTER(ALL('Table'),[Date]<SELECTEDVALUE('Table'[Date])))
Return
IF(N1<=3,N1,3)
Sales Growth =
Var N1=DIVIDE([Cumulative 3 month],[Count])
return
DIVIDE(SUM('Table'[Sales])-N1,N1)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Atinder ,
You can try the following methods.
Cumulative 3 month =
Var N1=SUMMARIZE(FILTER(ALL('Table'),[Date]<SELECTEDVALUE('Table'[Date])),[Date],"Sum",SUM('Table'[Sales]))
Var N2=TOPN(3,N1,[Date],DESC)
Var Cumulative3month=SUMX(N2,[Sum])
return
Cumulative3month
Count =
Var N1=CALCULATE(COUNT('Table'[Date]),FILTER(ALL('Table'),[Date]<SELECTEDVALUE('Table'[Date])))
Return
IF(N1<=3,N1,3)
Sales Growth =
Var N1=DIVIDE([Cumulative 3 month],[Count])
return
DIVIDE(SUM('Table'[Sales])-N1,N1)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
54 | |
39 | |
33 | |
31 | |
25 |