Forum Discussion
Time Intelligence Question
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.
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))
6 Replies
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))Thank you so, so much!
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] ) >= 90Alright, what would you refer it as?
Unknowncharacte Should be able to modify Open Tickets for this purpose:
Thank you so much for the reference!
