How to find weekly growth rate in power bi

[arvindyadav]

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

[Anonymous]

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]
    )

[arvindyadav]

Hi @Anonymous ,

It works but in 5th and 6th week it calculating wrong I guess Can you please suggest me what should I do?

[Anonymous]

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

[arvindyadav]

Hi @Anonymous ,

But when I select so,e account or account holder in its seems to wrong value in growth rate column.

[Anonymous]

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] )
        )

[arvindyadav]

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.

[v-yulgu-msft]

Hi @arvindyadav ,

Please share your sample .pbix file for check. Remember to mask sensitive data before uploading.

Regards,

Yuliana Gu

[parry2k]

@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] )
        )

[arvindyadav]

Hi @parry2k ,

Still not working.

[Anonymous]

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.

[arvindyadav]

Hi @Anonymous ,

When I clicked on any slicer then it will give me the wrong answer.