Creating a new measure from date ranges

[m080p695]

Hi, I have been struggling with trying to figure out how to create a new categorical measure based on date ranges within another column. For example, I have the column [request_date] with single date entries. I am trying to create a new measure [grant year] that takes a range of dates (for example, 2022-07-01 to 2023-06-30) and recodes those as "year 1" 

Copilot gave me the DAX code

Year =
SWITCH(
    TRUE(),
    'PBI vwFrontiers_Consultation_Request'[request_date] >= DATE(2022,7,1) && 'PBI vwFrontiers_Consultation_Request'[request_date] <= DATE(2023,6,30), "Year 1",
    'PBI vwFrontiers_Consultation_Request'[request_date] >= DATE(2023,7,1) && 'PBI vwFrontiers_Consultation_Request'[request_date] <= DATE(2024,6,30), "Year 2",
    'PBI vwFrontiers_Consultation_Request'[request_date] >= DATE(2024,7,1) && 'PBI vwFrontiers_Consultation_Request'[request_date] <= DATE(2025,6,30), "Year 3",
    'PBI vwFrontiers_Consultation_Request'[request_date] >= DATE(2025,7,1) && 'PBI vwFrontiers_Consultation_Request'[request_date] <= DATE(2026,6,30), "Year 4",
    'PBI vwFrontiers_Consultation_Request'[request_date] >= DATE(2026,7,1) && 'PBI vwFrontiers_Consultation_Request'[request_date] <= DATE(2027,6,30), "Year 5",
    "Outside Grant Period"
)

but whenever I input it, I get a syntax error. Either it doesn't recognize the variable "request_date" or says that Switch() is not a recognized function. 

[v-pnaroju-msft]

Thankyou, @lbendlin, @DataNinja777, @Praful_Potphodeand @Kedar_Pande for your responses.

Hi m080p695,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @lbendlin, @DataNinja777, @Praful_Potphode and @Kedar_Pande to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[Kedar_Pande · Accepted solution]

Create a calculated column, not a measure.

Grant Year =
SWITCH(TRUE(),
'PBI vwFrontiers_Consultation_Request'[request_date] >= DATE(2022,7,1) &&
'PBI vwFrontiers_Consultation_Request'[request_date] <= DATE(2023,6,30), "Year 1",
'PBI vwFrontiers_Consultation_Request'[request_date] >= DATE(2023,7,1) &&
'PBI vwFrontiers_Consultation_Request'[request_date] <= DATE(2024,6,30), "Year 2",
"Outside Grant Period"
)

Add more date ranges as needed.

[m080p695]

Thank you! You are correct. I needed to create a new column not a new measure. This worked for me. 

[Kedar_Pande]

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Praful_Potphode]

@m080p695 

The reasonyou are getting error is because measure expects single/scalar value. in your expression, column is being compared with a single date value.  it is not able to convert your expression to single/scalar value.instead of using request_date column try to put it in some aggregation like min ,max etc. and try again.

Year =
SWITCH(
    TRUE(),
    MIN('PBI vwFrontiers_Consultation_Request'[request_date]) >= DATE(2022,7,1) && 
    MIN('PBI vwFrontiers_Consultation_Request'[request_date]) <= DATE(2023,6,30), "Year 1",
    MIN('PBI vwFrontiers_Consultation_Request'[request_date]) >= DATE(2023,7,1) 
 && MIN('PBI vwFrontiers_Consultation_Request'[request_date])<= DATE(2024,6,30), "Year 2",
    MIN('PBI vwFrontiers_Consultation_Request'[request_date])>= DATE(2024,7,1) 
 && MIN('PBI vwFrontiers_Consultation_Request'[request_date]) <= DATE(2025,6,30), "Year 3",
    MIN('PBI vwFrontiers_Consultation_Request'[request_date]) >= DATE(2025,7,1) 
 && MIN('PBI vwFrontiers_Consultation_Request'[request_date]) <= DATE(2026,6,30), "Year 4",
    MIN('PBI vwFrontiers_Consultation_Request'[request_date]) >= DATE(2026,7,1) 
 && MIN('PBI vwFrontiers_Consultation_Request'[request_date])<= DATE(2027,6,30), "Year 5",
    "Outside Grant Period"
)

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[DataNinja777]

Hi @m080p695 ,

In order to create what you require, you can create a calculated column like below:

Grant Year = 
VAR d = Requests[request_date]
VAR fyStart = DATE ( YEAR(d) - IF ( MONTH(d) < 7, 1, 0 ), 7, 1 )
VAR yrs = YEAR(fyStart) - 2022
RETURN
IF(
    d < DATE(2022,7,1) || d > DATE(2027,6,30),
    "Outside Grant Period",
    "Year " & (yrs + 1)
)

The resultant output in table visual is as shown below:

Please find attached the pbix file for your reference.

Best regards,

[lbendlin]

If you want a measure then you need some sort of aggregation for your request_date  value.

For a shorter version you can use CALENDAR

Year =
SWITCH(
    TRUE(),
    'PBI vwFrontiers_Consultation_Request'[request_date] IN CALENDAR("2022-07-01","2023-06-30"), "Year 1",
   ...,
    "Outside Grant Period"
)