Calculating Downtime based on Start and End Date with filters

[faithdwn]

I have been struggling with this for a while and i will try to explain as best as i can what i am trying to achieve.

I have a query that has two date columns - Created Date and Modified Date. I have created a column to calculate how long that ticket has been 'open' for based on those two columns - Open Tickets Days = (Modified date - Created Date).

Now i want to create a measure-(Open>3 Days)- that calculates the Open Tickets Days based on Tickets that have been open for more than 3 days dependent on another column filter-(Status): Completed", "decomissioned". 

I have tried a quick measure but only able to filter one value for the Open Ticket Days which doesn't work and i can't figure out the DAX for this type of filter.

Esentially i want to be able to calculate Downtime = (Open >3 days)/(Total Open Tickets Days) 

How do i achieve this?

[v-piga-msft]

Hi @faithdwn ,

Have you solved the problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample and the expected output.

Best  Regards,

Cherry

[v-piga-msft]

Hi @faithdwn ,

If I understand your requirement correctly that you want to calculate the measure (Open>3 Days) which is based on the Status column filter: Completed", "decomissioned".

If so, from your logic, you could refer to this measure below and modify it based on your actual scenario.

measure =
CALCULATE (
    COUNT ( [Open Tickets Days] ),
    FILTER (
        ALLSELECTED ( 'tablename'[Status] ),
        MAX ( 'tablename'[Open Tickets Days] ) > 3
            && 'tablename'[Status] = SELECTEDVALUE ( 'tablename'[Status] )
    )
)

If you still need help, please share your data sample which could reproduce your scenario as table format and your desired output so that we could have  a test on it.

Best Regards,

Cherry