Forum Discussion

LizardBoi's avatar
LizardBoi
Regular Visitor
5 years ago
Solved

Assign text depending on Date

Hi All

Im looking for some help in creating a column in my date table that will allocate the season the date is in.

I need the information to be created regardless of year - but to include the year in the output....so


If the date is between 1st May '21 and 30th October '21 = "S21"

If the date is between 1st November '21 to 30th April '22 = "W21/22"
and so on.

Without going through each date in my table - whats the best way to do this please?

TIA

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

    Hey LizardBoi ,

     

    yes, you can do that with a calculated column. Try the following approach:

    Season = 
VAR vMonth = MONTH(myDateTable[Date])
VAR vYear = YEAR(myDateTable[Date])
RETURN
SWITCH(
    TRUE(),
    vMonth >= 5 && vMonth <= 10, "S" & RIGHT(vYear, 2),
    vMonth <= 4, "W" & RIGHT(vYear-1, 2) &" /" & RIGHT(vYear, 2),
    vMonth >= 11, "W" & RIGHT(vYear, 2)   & "/" & RIGHT(vYear+1, 2)
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

2 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey LizardBoi ,

     

    yes, you can do that with a calculated column. Try the following approach:

    Season = 
VAR vMonth = MONTH(myDateTable[Date])
VAR vYear = YEAR(myDateTable[Date])
RETURN
SWITCH(
    TRUE(),
    vMonth >= 5 && vMonth <= 10, "S" & RIGHT(vYear, 2),
    vMonth <= 4, "W" & RIGHT(vYear-1, 2) &" /" & RIGHT(vYear, 2),
    vMonth >= 11, "W" & RIGHT(vYear, 2)   & "/" & RIGHT(vYear+1, 2)
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis