Switch in calendar

[Brendy_P]

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())

[mahoneypat]

Please see if this article helps. It has a Date table created in the query editor that may work for you.

445 Calendar with 53-Week Years – Hoosier BI

Pat

[Brendy_P]

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

[Brendy_P]

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

[serpiva64]

Sorry,

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