switch using date ranges

[Anonymous]

I have a DATE table with the following columns
Date (DATE format), (the following columns are all Whole Numbers) WeekNum, Month, Day, Year, Qtr, End Date (DATE format), SprintNumber (whole number) (this is a calculated column I'm using the SWITCH function in)

I'm trying to put a number from 1-8 in this SprintNumber column

SprintNumber 1 = dates between 8/1/2019 and 10/31/2019

SprintNumber 2 = dates between 11/1/2019 and 1/31/2020

SprintNumber 3 = dates between 2/1/2020 and 4/31/2020

SprintNumber 4,5,6,7,8 every 3 months following sprint3

I have the following formula and don't know what I'm doing wrong (I only have the first 3 sprints, but would add 4-8)

SprintNumber = SWITCH(

'Date Table'[Date] >= DATE(2019,8,1),1,

'Date Table'[Date] >= DATE(2019,11,1),2,

'Date Table'[Date] >= DATE(2020,1,1),3)

I have also tried - but this didn't work either

I have tried using DATESBETWEEN etc. and other variations, but can't figure this out. 

Thanks in advance ! 

Jim

[Geradav]

@Anonymous The FORMAT() function "Converts a value to text according to the specified format." (Source FORMAT function (DAX) - DAX | Microsoft Docs)

Therefore you would be comparing a number to a text data type.
Also, passing a number as text data type as argument to the VALUE() function is the same as just writing a literal number.

I agree with @amitchandak  that if you have some sample PBIX that you can share with us, it would help in the process of helping you.

David

[Anonymous]

@Geradav Thanks for your help on this. Please see my most recent update. I have cleaned up the function. But I'm not getting the results for sprintnumber 2, or 4. 

Thanks in advance,

Jim

[amitchandak]

@Anonymous , Formula seems fine. The only doubt I have is that either Month of year is of data type text.

Can you share a sample pbix after removing sensitive data?

[Anonymous]

Hello! - I'm making progress but still not there - The below function is not giving any errors, but Its giving a "1" for starting 8/1/2019 but NOT giving a "2" for the respective dates. It IS returning a "3" for dates starting 1/1/2020. Not sure what its not giving a 2 or 4

[Anonymous]

I didn't get any errors using this (I changed the way the calendar table was created - using 

Date Table = CALENDAR(Date (2018, 1, 1), DATE( 2022, 12, 31))

Then I used this method: BUT I didn't get ANY results. 

[Anonymous]

Here is the DATE table - how I created it. 

[Anonymous]

I can't send the pbix. However here is a screen shot - YEAR is the same type. 

I have also tried this 

[amitchandak]

@Anonymous , do not use MAX as this column max will take max date. Also, remove values

like

Switch(true(),

Date[Month] = 8 && Date[Year] =2009 , 1,

Date[Month] = 11 && Date[Year] =2009 , 2

)

Also in case you trying some custom financial calendar refer my blog

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

[Anonymous]

@amitchandak Thank you - I cleaned it up a bit. Its not seeing the second line where month = 11 and month = 4. Its only giving results as Sprintnumber 1 and sprint number 3. When month = 11 or greater, it doesn't give the result sprintnumber 2

[Geradav]

@JPiazza

Start with the highest month number first like this:

SprintNumberTM
    SWITCH ( TRUE(),
        'Date Table'[Month] > 11 && 'Date Table'[Year] - 2019, 2,
        'Date Table'[Month] > 8 && 'Date Table'[Year] - 2019, 1,
        'Date Table'[Month] > 4 && 'Date Table' [Year] - 2020, 4,
        'Date Table'[Month] > 1 && 'Date Table'[Year] 2020, 3
)

In your case, if you start evaluating a month number greater than or equal to 8 when the function checks whether month 12 is greater than 8, it returns TRUE(), and therefore stops the SWITCH() evaluation and returns a value of 1, and does not go to the second evaluation statement.

If you start with the highest number, the function first checks whether the month number is greater than or equal to 11, if you do not continue with the second evaluation statement.

E.g.

•> Is month 5 greater than or equal to 11 --> False (continue with the following eval statement)

•> Is month 5 greater than or equal to 8 --> False (continue with the following eval statement)

•> Is month 5 greater than or equal to 4 --> True (stops evaluation)

•> Returns 4

Does it make sense?

I hope that helps. Let us know

David

[Anonymous]

I found this solution which is working correctly - 

Thank you for your responses to help me figure this out! 

Jim

[Anonymous]

Hi David - thanks for your help! It does, however I'm using an AND - not OR- won't it evaluate the entire line? 

Thanks,

Jim