Aged tickets calculation using measure

[Anonymous]

Hi all, 

I'm trying to calculate the date duration to find out the number of aged tickets per selected month.

The idea is the date duration changes dynamically based on the user select. 

I created a measure to calculate the duration when a ticket is not closed (closed date is empty) and it's working okay.

Aged Tickets Not closed = 
var _max = MAXX(DateKey, DateKey[Date])
var not_closed = CALCULATE(
    DATEDIFF(
        MINX('All Tickets', 'All Tickets'[Created Date]),
        _max, DAY),
    FILTER(
        ALL(DateKey), 
        MONTH(DateKey[Date])
    )
)

return not_closed

Result ->

Created Date - Jul 30, 2020 = 171 Days, 161, 143...

The problem is when a ticket has a closed date, it should return all tickets closed in the selected month, but it only shows tickets created in the selected month. 

var _max_c = MAXX('All Tickets', 'All Tickets'[Closed Date])
var closed = CALCULATE(
    DATEDIFF(
        MINX('All Tickets', 'All Tickets'[Created Date]),
        _max_c, DAY),
    FILTER(
        ALL(DateKey), 
        MONTH(DateKey[Date])
    ),
    USERELATIONSHIP('All Tickets'[Closed Date], DateKey[Date])
)
return closed

Result ->

I want to get all closed ticket with "Aged Tickets Not closed" value in the selected month, not created. 

Eventually what I want to achieve is to get aged tickets per selected month (Over "30 days", "60 days", "90 days' and so on) so the date calculation should be flexible. 

I've tried with column as well, it works fine with the closed date but it gives 0 when the ticket is in 'pending' status (closed date blank). 

I've been struggling with this for a couple of days, tried with columns, changing filters but no luck so far. 

Any help or tips would be appreciated. 

Thank you in advance! 🙂  

[Anonymous]

Thank you @AntrikshSharma and @amitchandak for the response and sorry for the late reply!

Unfortunately, I cannot share the data as it is confidential. 

What I'm trying to do is, 

I want to calculate the duration based on the selected month. 

For example, list all closed tickets in July with date difference between created and closed.

Now it only lists created tickets in July when I select July.

Hope this made it clear what I want to achieve here 🙂 

[amitchandak]

@Anonymous , hope you have checked this -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Open is July- Based on Open date join to date table. We need use everything related to time from date table (Date, Month, qtr year etc)

Closed in July - Open any time but have a closed date. In this we need a closed date join with Date table.  Use Userelation and use this inactive join on close date. Follow HR like or video -https://youtu.be/e6Y-l_JtCq4

Open in July , closed  - Here, you need to work with the open date and close date is not blank

Need different slicer for open and close. In this case, you can use from dates in the table or need to have two date table. refer How I created two tables and created a cohort -https://youtu.be/Q1vPWmfI25o

[Anonymous · Accepted solution]

Thank you for the quick response @amitchandak 

I have read and looked through your blog, .pbix, and youtube videos but couldn't figure out what the problem was, however, I managed to solve the issue by not using variables which is odd.

This is the solution I got it working. 

Aged Tickets Not closed = 
var closed = CALCULATE(
    DATEDIFF(
        MINX('All Tickets', 'All Tickets'[Created Date]),
        MAX('All Tickets'[Closed Date]), DAY),
    FILTER(
        ALL('All Tickets'[Closed Date]), 
        MONTH('All Tickets'[Closed Date])
    ),
    USERELATIONSHIP(DateKey[Date], 'All Tickets'[Closed Date])
)

return closed

 This is the result I wanted to get. ->

Before this solution, I was using the variables like below, and it showed Opened and Closed tickets only in a selected month. (so the date difference never went over 30 days)

var _max_c = MAX('All Tickets'[Closed Date])
var _min = MINX('All Tickets', 'All Tickets'[Created Date])

var closed = CALCULATE(
    DATEDIFF(
       _min,
       _max_c, DAY),
    FILTER(
        ALL('All Tickets'[Closed Date]), 
        MONTH('All Tickets'[Closed Date])
    ),
    USERELATIONSHIP(DateKey[Date], 'All Tickets'[Closed Date])
)

return closed

I don't understand what was playing up in behind the scene, but it is working okay now since I changed the DAX.

But thank you for your help, I really appreciate it!!

[amitchandak]

@Anonymous , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Refer this file

https://www.dropbox.com/s/excoqikgpyz5zvk/Current_employee.pbix?dl=0

[AntrikshSharma]

This part is just returning the full date table FILTER ( ALL ( DateKey ), MONTH ( DateKey[Date] ) )

What are you trying to do with this? Also can you share the PBIX file?