Growth Week on Week

[vjnvinod]

Dear Experts,

i have a requirement to create week on week growth rate

below is how the  table looks

Growth should be calculated based

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

here is the sample data

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

then i need to make a visual like the below in power BI Desktop, its an example of Expected Output(based on Week on Week growth for Coloumn3)

also note that P8 Wk2(what you see in my data, is Wk of 10 Feb and so on for other periods and week

if anyone can do this in power BI desktop, can you please share pbix file as well?

[Greg_Deckler]

Yeah, that's going to be extra nasty because you have weird text weeks. I would recommend coming up with a sequential number for your weeks and then it is trivial. See these links:

This one compares a row with another "previous" row.

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

And this is a Sequential number for weeks:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116

[vjnvinod]

@Greg_Deckler 

@amitchandak 

if i rank my weeks,

let say P4 Week1 is 1

P4 Week2 is 2 and so on

can you tell me DAX  formula to create a week on week measure

in Excel i have used below formula

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

here is my data again

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

[littlemojopuppy]

To pile on what @Greg_Deckler  said, this is very easy to accomplish if you use dates and some basic time intelligence functions.  Is it at all possible to translate P4 Week 1 into dates?  Assuming that P4 refers to Period 4 which could be 4/1 through 4/30 (or whatever they are).  You include the period and week numbers in the date table and this becomes rather simple to accomplish.

[amitchandak · Accepted solution]

@vjnvinod , Please refer to my file, Where I am already doing that. Hope it helps

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

[vjnvinod]

@amitchandak 

thanks, its really close

however My date table should basically start from July to June(Fiscal year for my org) and your logic is based on Calendar year

can you see if you can change in the pbix i have shared below?

and also in addition to what you have in date coloumn

i need 1 more coloumns one for "WeekPeriod"

and logic for weekperiod is

if my Week start date is 7/1/2019: then it should be P1 Wk1

if my Week start date is 7/8/2019: then it should be P1 Wk2 and so on til P1 Wk4 (for the month of July)

and for the month of august, same above logic applies, instead of P1, it will become P2 and combination of weeks

and this way it should go till June 2020

This coloumn is needed to connect my source data to the date table

here is my pbix file, i have replicated the date table from the pbix you shared

https://drive.google.com/file/d/1rq0jvs6DeidfLlkmdo04W24RUAVVC1o1/view?usp=sharing

[littlemojopuppy]

You can create more fields in your date table to accommodate the different fiscal periods...

FicalMonthNumber =
SWITCH(
	Date[MonthNumber]
	7, 1
	8, 2,
	9, 3,
	10, 4,
	11, 5,
	12, 5,
	1, 7,
	2, 8,
	3, 9,
	4, 10,
	5, 11,
	6, 12,
	BLANK()
)

Do the same with converting calendar to fiscal year.  And then the logic should work

[vjnvinod]

@littlemojopuppy 

thanks for the help!

unfortunately i am not that familiar with DAX.

here is my pbix, can you help me to build this logic?

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

i am actually looking for trend graph like below thats the output from the whole exercise

also i realised that there has to be one more logic

if there is no week data available for any of the week, then week on week growth should be calculated based on the Previous week of the week data which is missing

[littlemojopuppy]

In your date table add four calculated columns:

MonthNumber = MONTH('Date'[Date])

MonthName = FORMAT('Date'[Date],"MMMM") (this is nice to have)

FicalMonthNumber = 
    SWITCH(
	    'Date'[MonthNumber],
	    7, 1,
	    8, 2,
	    9, 3,
	    10, 4,
	    11, 5,
	    12, 6,
	    1, 7,
	    2, 8,
	    3, 9,
	    4, 10,
	    5, 11,
	    6, 12,
        BLANK()
    )

FicalYear = 
    SWITCH(
	    'Date'[MonthNumber],
	    7, YEAR('Date'[Date]) + 1,
	    8, YEAR('Date'[Date]) + 1,
	    9, YEAR('Date'[Date]) + 1,
	    10, YEAR('Date'[Date]) + 1,
	    11, YEAR('Date'[Date]) + 1,
	    12, YEAR('Date'[Date]) + 1,
	    1, YEAR('Date'[Date]),
	    2, YEAR('Date'[Date]),
	    3, YEAR('Date'[Date]),
	    4, YEAR('Date'[Date]),
	    5, YEAR('Date'[Date]),
	    6, YEAR('Date'[Date]),
        BLANK()
    )

That should set you up to use time intelligence functions while having a fiscal calendar that is not calendar year.  Bear in mind that some time intelligence functions have a parameter of year_end_date so pay attention and refer to the documentation.  You can get more info on time intelligence functions here.

[vjnvinod]

@littlemojopuppy 

thanks for this

Can you give me the  Coloum measure for Wk1, Wk2...Wk12 as well?

also i would like to create another coloumn which can give me coloumns like "Week Ending July 5", based one the week end date

after getting the weeks

i would like to comibine Fiscal Month and period, i think i will merge the coloumn, so that will create a coloumn lik P1Wk1.,P1Wk2.......

how would i rank that coloumn then?