Forum Discussion

m080p695's avatar
m080p695
Regular Visitor
10 months ago
Solved

Creating a new measure from date ranges

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. 

  • Kedar_Pande's avatar
    Kedar_Pande
    10 months ago

    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.

     

7 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    10 months ago

    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,

     

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    10 months ago

    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.

     

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    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.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    10 months ago

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

     

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    10 months ago

    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