Week on Week Grwoth measure help

[vjnvinod]

Hi Experts

I would like to create a week on week growth rate

below is the table

Growth should be calculated based

(Current week GLobal Ter/Previous Week Global TER)-1

then i need to make a visual like this in power BI

below i did it in excel using the above data, but not sure how to do this in power BI

[V-lianl-msft]

Hi @vjnvinod ,

If you get the value of the previous week in the current week, the problem should be simpler.

last_week =
VAR last_week =
    CALCULATE (
        FIRSTNONBLANK ( 'data (2)'[GlobalTER], 1 ),
        FILTER (
            'data (2)',
            'data (2)'[Channel] = EARLIER ( 'data (2)'[Channel] )
                && 'data (2)'[period] = EARLIER ( 'data (2)'[period] )
                && 'data (2)'[weeknum]
                    = EARLIER ( 'data (2)'[weeknum] ) - 1
        )
    )
RETURN
    IF ( ISBLANK ( last_week ), 'data (2)'[GlobalTER], last_week )

Please refer to this pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vjnvinod]

@V-lianl-msft 

hey thanks so much, this

is what exactly i was trying to do but 2 conditions

1) if there is no data for any of the weeks, then use the previous week (of the week where there is no data available) to calculate the growth

2) I also  noticed for P5Wk1, growth should be calculated based on P4Wk5 data and so on for any of the week begining period, but its not how this is done in your pbix, hence it showing growth 0%

@V-lianl-msft 

Can you bring in the above 2 conditions and share the pbix? so i can close this thread?

thanks for much for your help

[V-lianl-msft]

Hi @vjnvinod ,

Please sort the columns in edit queries and then add an index column.

The sample pbix has been updated.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vjnvinod]

@V-lianl-msft 

i was trying to do this in my dataset in the below link, using the measure and coloumns you created but wasn't able to replicate that and was throwing up error.

can you check if you can replicate that into the below data?

https://drive.google.com/open?id=19efixTVL4m86b0vMKaFGlEeHWPqMzRSy

[amitchandak · Accepted solution]

For the week, you have to create week rank, based on the week start date or any other column equivalent of that. Then you can this week, last week easily.

Refer : https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[vjnvinod]

@amitchandak 

thank you for the response

hey unfortunately not working for me

here is my sample data, can you let me know if its possible for you

https://drive.google.com/open?id=1udtnUgUku4T1UTKUyKVVXI9nonggwiya

my final outcome in pbix should be  like below

[vjnvinod]

@amitchandak 

hi, just checking to see if you can help

i have tried, but its not working for me