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