topic Creating a new measure from date ranges in DAX Commands and Tips

Creating a new measure from date ranges

m080p695 — Fri, 24 Oct 2025 15:00:40 GMT

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.

Re: Creating a new measure from date ranges

lbendlin — Fri, 24 Oct 2025 23:09:03 GMT

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" )

Re: Creating a new measure from date ranges

DataNinja777 — Sat, 25 Oct 2025 15:03:10 GMT

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,

Re: Creating a new measure from date ranges

Praful_Potphode — Mon, 27 Oct 2025 05:53:29 GMT

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

Re: Creating a new measure from date ranges

Kedar_Pande — Mon, 27 Oct 2025 07:11:34 GMT

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.

Re: Creating a new measure from date ranges

v-pnaroju-msft — Mon, 27 Oct 2025 17:43:56 GMT

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.

Re: Creating a new measure from date ranges

m080p695 — Mon, 27 Oct 2025 18:07:49 GMT

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

Re: Creating a new measure from date ranges

Kedar_Pande — Tue, 28 Oct 2025 13:05:47 GMT

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