Forum Discussion

StephenK's avatar
StephenK
Icon for Resolver I rankResolver I
6 years ago
Solved

Calculating Flu Season from Date Table

Hey all,   I have a date table containing all dates within the last 3 years. I need to create a calculated column either in power query or dax that identifies the flu season. So for dates between 1...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    This column expression should work in your Date table

     

    Flu Season =
    SWITCH (
    TRUE (),
    Flu[Month] <= 3, YEAR ( Flu[Date] ) - 1 & "-"
    & YEAR ( Flu[Date] ),
    Flu[Month] >= 10, YEAR ( Flu[Date] ) & "-"
    & YEAR ( Flu[Date] ) + 1,
    "Off Season"
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi StephenK 

Please provide sample Input and output.

StephenK's avatar
StephenK
Icon for Resolver I rankResolver I
6 years ago

Anonymous 

Date                                                Year   Month  FluSeason

Tuesday, September 25, 201820189Off Season
Wednesday, September 26, 201820189Off Season
Thursday, September 27, 201820189Off Season
Friday, September 28, 201820189Off Season
Monday, October 1, 20182018102018 - 2019
Tuesday, October 2, 20182018102018 - 2019
Wednesday, October 3, 20182018102018 - 2019
Thursday, October 4, 20182018102018 - 2019
Friday, October 5, 20182018102018 - 2019
Monday, October 8, 20182018102018 - 2019
Tuesday, October 9, 20182018102018 - 2019
Etc.Etc.Etc.Etc.
Thursday, March 28, 2019201932018 - 2019
Friday, March 29, 2019201932018 - 2019
Saturday, March 30, 2019201932018 - 2019
Sunday, March 31, 2019201932018 - 2019
Monday, April 1, 201920193Off Season
  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    This column expression should work in your Date table

     

    Flu Season =
    SWITCH (
    TRUE (),
    Flu[Month] <= 3, YEAR ( Flu[Date] ) - 1 & "-"
    & YEAR ( Flu[Date] ),
    Flu[Month] >= 10, YEAR ( Flu[Date] ) & "-"
    & YEAR ( Flu[Date] ) + 1,
    "Off Season"
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI StephenK 

     

    Use the below logic.

    IF(MONTH(table[date])>=8 && MONTH(table[date]) <=3

                                      ,IF(MONTH(table[date]) >=8, YEAR(table[date]), YEAR(table[date])-1) & " - " IF(MONTH(table[date]) >=8, YEAR(table[date])+1, YEAR(table[date]))

                                      ,"Off Season")