Using switch true in Power BI with dates

user_4493548 0
Viewed 2432

I'm working on a problem in Power BI to categorize rows based on a 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 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")
dax powerbi
2 Answers

Olly has a good suggestion but it can be simplified using 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

It's also worth noting that in your SWITCH function, August 30th and 31st are unaccounted for each year. I'm guessing that's not intentional.

user_7652260

That's an inefficient way to deal with it, when your value may be derived.

Try

New Column = 
VAR StartYear = 
    YEAR ( Dates[Date] ) + 
    IF ( 
        MONTH ( Dates[Date] ) < 9,
        0,
        1
    )
RETURN
    StartYear & "/" & StartYear + 1
user_84665230
Related