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

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

    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'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.