Need help creating "bucket categories" from helpdesk tickets

[jdubbs57]

Hello,

I am attempting to take helpdesk ticket data and create "buckets" of various day ranges to show how long tickets were open. For example, 1-5 days, 6-10 days, etc.

 My first calculation is a DATEDIFF. 

                   _Days between = DATEDIFF('CE_incident_Export_Data'[Created Date Time],'CE_incident_Export_Data'[Closed Date Time],DAY)

Power BI validates the formula and also labels it as "numeric calculated column"

When I attempt to create my next formula (IF) to create the buckets it wont let me select the newly created field above.

This is what I imagine the forumla to look like but it wont let me use _Days between field.

_Age Category = IF(

'CE_incident_Export_Data'[_Days between] <=10,

"Up to 10 days",

IF(

CE_incident_Export_Data[_Days between] >=11,

"11 to 25 days"

)

)

Any help or guidance on how to change the field type or another way to create the 2nd formula is appreciated.

As always I can supply some mock data if needed.

[Anonymous · Accepted solution]

Hi @jdubbs57 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create calculated columns.

_Days between = DATEDIFF('CE_incident_Export_Data'[Created Date Time],'CE_incident_Export_Data'[Closed Date Time],DAY)

_Age Category = SWITCH(TRUE(),'CE_incident_Export_Data'[_Days between]<=10,"Up to 10 days",'CE_incident_Export_Data'[_Days between]>=11,"11 to 25 days",blank())

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[FreemanZ]

@jdubbs57 

try to add a column with this:

 

_Age Category = 

VAR Duration = [Closed Date Time] - [Created Date Time]

RETURN 

IF(

    Duration<=10,

    "Up to 10 days",

    IF(

        Duration >=11,

        "11 to 25 days"

    )

)