How can I get weekly growth rate in power BI?
|Weekly Growth Rate|
|Week num||Profit||Growth Rate|
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] )
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
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 @Nick_M ,
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.