Calculating Flu Season from Date Table

[StephenK]

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!

[pranit828]

Hi @StephenK 

Please provide sample Input and output.

[StephenK]

@pranit828 

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 · Accepted solution]

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

[pranit828]

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