Forum Discussion
Resolver ICalculating 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 10/1/18 and 3/31/20, I would need an output of 2018 - 2019 Season, same for 2019 - 2020 and so on. How would I do this?
Thanks!
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
4 Replies
Hi StephenK
Please provide sample Input and output.
StephenKAnonymous
Date Year Month FluSeason
Tuesday, September 25, 2018 2018 9 Off Season Wednesday, September 26, 2018 2018 9 Off Season Thursday, September 27, 2018 2018 9 Off Season Friday, September 28, 2018 2018 9 Off Season Monday, October 1, 2018 2018 10 2018 - 2019 Tuesday, October 2, 2018 2018 10 2018 - 2019 Wednesday, October 3, 2018 2018 10 2018 - 2019 Thursday, October 4, 2018 2018 10 2018 - 2019 Friday, October 5, 2018 2018 10 2018 - 2019 Monday, October 8, 2018 2018 10 2018 - 2019 Tuesday, October 9, 2018 2018 10 2018 - 2019 Etc. Etc. Etc. Etc. Thursday, March 28, 2019 2019 3 2018 - 2019 Friday, March 29, 2019 2019 3 2018 - 2019 Saturday, March 30, 2019 2019 3 2018 - 2019 Sunday, March 31, 2019 2019 3 2018 - 2019 Monday, April 1, 2019 2019 3 Off Season - mahoneypat
Microsoft Employee
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
