I have a DATE table with the following columnsDate (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/2019SprintNumber 2 = dates between 11/1/2019 and 1/31/2020SprintNumber 3 = dates between 2/1/2020 and 4/31/2020SprintNumber 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

@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 , 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?

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

Here is the DATE table - how I created it.

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.

switch using date ranges | Microsoft Fabric Community

12 Replies

amitchandak
Super User
5 years ago
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
  Not applicable
  5 years ago
  I can't send the pbix. However here is a screen shot - YEAR is the same type.
  I have also tried this
  - amitchandak
    Super User
    5 years ago
    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
  Not applicable
  5 years ago
  Here is the DATE table - how I created it.
- Anonymous
  Not applicable
  5 years ago
  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.
Geradav
Responsive Resident
5 years ago
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
  Not applicable
  5 years ago
  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

Forum Discussion

switch using date ranges

12 Replies

Related content

Using Switch

Using Range of Values with SWITCH(TRUE Function

Toggle Switches Using Images

Alternative to SWITCH ()?

Using switch in M