Forum Discussion

MH3's avatar
MH3
Helper V
5 years ago
Solved

Seasonal Week Column in Date Table

Hi,
I have a Simple Date Table which is from January to December in which I have columns.
(Date, Month Number, Week No, Week Name and Year)

I have a Season Column in which I have divided the Months into Four Seasons (Summer, Winter, Spring and Autumn) 

Season = SWITCH(TRUE(),
DimDate[Month Number] in {12,1,2},"Winter",
DimDate[Month Number] in {3,4,5},"Spring",
DimDate[Month Number] in {6,7,8},"Summer","Autumn")

Now, I want to create a Seasonal Week Column, in which each Season have 3 months and it should have its corresponding week numbers and should refresh on every Season.

Week Start from Monday - Week End on Sunday

I am confused in creating the logic behind it. 

Any Help?

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    MH3 , Try new columns like these in date tbale

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    min week start = minx(filter('Date',[Year] =earlier([Year]) && [Season] =earlier([Season])),[Week Start date])
    season week No = quotient(datediff([min week start],[date],day),7)+1

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    MH3 , Try new columns like these in date tbale

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    min week start = minx(filter('Date',[Year] =earlier([Year]) && [Season] =earlier([Season])),[Week Start date])
    season week No = quotient(datediff([min week start],[date],day),7)+1

    • MH3's avatar
      MH3
      Helper V
      5 years ago

      Thanks amitchandak 

       

      your solution solved the problem, but I am still confused on the logic you have used for Week Start Date Min Week Start No and season week No.
      I am unable to understand the logic.

       

      would you mind explaining it? Please