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
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?