Time Intelligence Question

[Unknowncharacte]

Hi, 

I need to create a measure to calculate # of cases that were opened for longer than 90 days and visualize it on a historical line chart. So, something like if time frame between date open and date closed is equals to 90 or more days then count that or if date closed is blank AND date opened to today is equals to, or more than 90 days then count that too.

I do not understand time calculations well enough to do this myself, so I am asking for help. Attaching a sample of my dashboard as well. 

Sample Data.pbix

[Greg_Deckler]

@Unknowncharacte Should be able to modify Open Tickets for this purpose:

Open Tickets - Microsoft Fabric Community

[Unknowncharacte]

Thank you so much for the reference!

[ThxAlot]

Your question has nothing to do with Time Intelligence functions.

Use COALESCE() to replace blank Closed Date on the fly.

= COALESCE( MAX( 'Fact Table'[Date Closed] ), TODAY() ) - MAX( 'Fact Table'[Date Opened] ) >= 90

[Unknowncharacte]

Alright, what would you refer it as?

[vojtechsima · Accepted solution]

Hello, @Unknowncharacte ,

try something like this:

LongerThan90 = COUNTROWS(FILTER('Fact Table', ( IF( ISBLANK('Fact Table'[Date Closed]), TODAY(), 'Fact Table'[Date Closed])  - 'Fact Table'[Date Opened] ) > 90))

[Unknowncharacte]

Thank you so, so much!