Using switch true in Power BI with dates

Question

I'm working on a problem in Power BI to categorize rows based on date criteria. My first idea was to use a nested if statement but realized that the switch(True() function might be better suited. Unfortunately for some reason, the statement is skipping from [Result2] to [Result3] and is not considering any criteria after the first.

I have a continuous date range and basically, if dates fall between w and x I want to return a specific value, and if dates fall between y and z I want to return another value.

I'm using a New Column, and the date is formatted as a date. My formula is as follows:

Column 2 = 

SWITCH(TRUE(),

   Dates[Date]>01/01/2007 && Dates[Date]<08/30/2008,"2007/2008",

   Dates[Date]>08/31/2008 && Dates[Date]<08/30/2009,"2008/2009",

   Dates[Date]>08/31/2009 && Dates[Date]<08/30/2010,"2009/2010",

   Dates[Date]>08/31/2010 && Dates[Date]<08/30/2011,"2010/2011",

   Dates[Date]>08/31/2011 && Dates[Date]<08/30/2012,"2011/2012",

   Dates[Date]>08/31/2012 && Dates[Date]<08/30/2013,"2012/2013",

   Dates[Date]>08/31/2013 && Dates[Date]<08/30/2014,"2013/2014",

   Dates[Date]>08/31/2014 && Dates[Date]<08/30/2015,"2014/2015",

   Dates[Date]>08/31/2015 && Dates[Date]<08/30/2016,"2015/2016",

   Dates[Date]>08/31/2016 && Dates[Date]<08/30/2017,"2016/2017", 

   "Unaccounted")

Answer 1

• use the time intelligence function STARTOFYEAR where the second argument indicates the year-end.

New Column = 

VAR StartYear = YEAR(STARTOFYEAR(Dates[Date], "08/31"))

RETURN StartYear & "/" & StartYear + 1

• In your SWITCH function, August 30th and 31st are unaccounted for each year which doesn't matter actually.

Want to become a Business Analyst? Then have a look at Power BI Microsoft certification course from Intellipaat!