Forum Discussion

Brendy_P's avatar
Brendy_P
Icon for Helper I rankHelper I
4 years ago

Switch in calendar

Hi Folks

2 things I am looking answers for if possible, the formula below returns the period based on the calendar [week]. So weeks 1,2,3,4 = period 1,     5,6,7,8 = period 2    9,10,11,12,13 =period 3 and so on up to 52. I want to reduce this formula, I tried using curly brackets but it gives me an error. Secondly, I would prefer to get the desired result in the query editor using a custom column if possible, thanks for your time.

 

Periods = SWITCH(
'Calendar'[Weeks],
1,1,2,1,3,1,4,1,
5,2,6,2,7,2,8,2,
9,3,10,3,11,3,12,3,13,3,
14,4,15,4,16,4,17,4,
18,5,19,5,20,5,21,5,
22,6,23,6,24,6,25,6,26,6,
27,7,28,7,29,7,30,7,
31,8,32,8,33,8,34,8,
35,9,36,9,37,9,38,9,39,9,
40,10,41,10,42,10,43,10,
44,11,45,11,46,11,47,11,
48,12,49,12,50,12,51,12,52,12,
BLANK())
Need Help

4 Replies

    • Brendy_P's avatar
      Brendy_P
      Icon for Helper I rankHelper I
      4 years ago

      Hi Pat

      Brillant!!! i have just watch your Youtube video and have learnt a little bit more on my journey about Power BI and Power Query. I will use your formula going forward and adapt it to what I need but I not sure if solves my initial question. No matter what I will dig deeper into your tutorial and hopefully learn a bit more thanks again

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Sorry,

    which is the rule to decide if period is made of 4 or 5 weeks?

     

  • Brendy_P's avatar
    Brendy_P
    Icon for Helper I rankHelper I
    4 years ago

    Hi Serpiva

    If I can explain every 3rd, 6th, 9th and 12th period consists of 5 weeks. So weeks 1-4 is period 1, weeks 5-8 is period 2, so weeks 9-13 is period 3 then revert back to 14-17 which is 4 weeks for period 4. Essentially periods 3,6,9,12 all are 5 week periods, all other periods are 4 weeks. Hope this helps