Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
6 years ago
Solved

Growth Week on Week

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 h...
Need Help
Show and Tell
vjnvinod's avatar
vjnvinod
Impactful Individual
6 years ago

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's avatar
littlemojopuppy
Community Champion
6 years ago

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's avatar
    vjnvinod
    Impactful Individual
    6 years ago

    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?