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