Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Time intelligence : Open/closed status

Hi,

I have a table of support cases that are either open or closed. 

All the case ID have a start/creation date and if they are closed they will get the closed date.

If the case is still open the closed date is blank.

 

I want to create a overview of the trend over time on open cases. I get the current situation, because the cases only have on status at the time. 

Lets say a case has been closed 11.november and created 1.october . I want the report to tell me that on the 1. november the case was still open.  

Is there possible to create some DAX logic that can help me with this? 

Help would really be appriciated 🙂
Best regards

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create a measure.

     

    Measure =
VAR _closed =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = "end"
                && 'Table'[id] = SELECTEDVALUE ( 'Table'[id] )
        )
    )
RETURN
    IF ( MAX ( 'Table'[date] ) = _closed, "closed", "open" )

     

     

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

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

     

     

2 Replies