Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
How can I get weekly growth rate in power BI?
example:-
Weekly Growth Rate | ||
Week num | Profit | Growth Rate |
4 | 5000 | 0% |
5 | 5466 | 9% |
6 | 2452 | -55% |
7 | 24578 | 902% |
8 | 2120 | -91% |
9 | 20000 | 843% |
10 | 15246 | -24% |
Thanks,
Arvind
Using the data you supplied you can do the following:
Total Profit = SUM ( Table1[Profit] ) Previous Week Profit = CALCULATE( [Total Profit], FILTER( ALL ( Table1), MAX(Table1[Week num]) -1 = Table1[Week num] ) ) Weekly Growth Rate = IF ( NOT (ISBLANK( [Previous Week Profit] )), DIVIDE( [Total Profit] , [Previous Week Profit] ) -1)
You dont need to ahve the Previous Week Profit measure on the table, just left it on there. Also, Assuming you have more than one year of data you will need to account for that as well. Meaning in the Previous Week Profit need to account for the year:
Previous Week Profit = CALCULATE( [Total Profit], FILTER( ALL ( Table1), MAX(Table1[Week num]) -1 = Table1[Week num] ), MAX ( Table1[Year]) = Table1[Year] )
Hi @Anonymous ,
It works but in 5th and 6th week it calculating wrong I guess Can you please suggest me what should I do?
the numerator just needs to be the Total Profit measure, which will be the total profit in the current filter context. Then denomniator is the Previous Week Profit measure, which will take the week number in the current filter context and go back 1 week and return that Total Profit. THen from there subtract the whole thing by 1 to get the %.
The If ( not ( isblank).. just ensures that there must be a previous week profit in order to have a weekly growth rate:
I dont seem to have that problem when I use Week Number as a slicer
Hi @Anonymous ,
But when I select so,e account or account holder in its seems to wrong value in growth rate column.
I see. It is because the Filter ( ALL ( Table) in the Previous week profit was removing all the filters so it would give the previous week profit for all the accounts. If you change it to the following (where it only removes the the Week Number column from the current filter context it shoud work:
Previous Week Profit = CALCULATE( [Total Profit], FILTER( ALL ( Table1[Week num]), MAX(Table1[Week num]) -1 = Table1[Week num] ) )
Hi @Anonymous ,
Thanks for your reply Your solution is work for me but when I click on any specific account from Slicer then it reflects the wrong %. Please find an IMAGE.
when you focus on week no 7 & 8 you will know what exactly happens in the formula.
Hi @arvindyadav ,
Please share your sample .pbix file for check. Remember to mask sensitive data before uploading.
Regards,
Yuliana Gu
@arvindyadav it is always good to share the whole dataset and how you are going to use it slicers, etc. coz that helps to find out the best solution.
Assuming you are using slicer for other columns, change your following DAX
Previous Week Profit = CALCULATE( [Total Profit], FILTER( ALLSELECTED ( Table1 ), MAX(Table1[Week num]) -1 = Table1[Week num] ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Like others have said, please share what is "not working". There are probably things on your end that are causing a problem that we simply do not know exists, so that makes it impossible to account for.
Hi @Anonymous ,
When I clicked on any slicer then it will give me the wrong answer.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |