How to sumarise data using two dates for case resolution reporting

[xr]

Hi all, I have a dataset representing incidents - where each incident has a 'received date' and a 'resolution date'. I'm looking to generate a table which summarise over the previous days the number of unresolved incidents at the start of the day, the new incidnents added that day, the incidents resolved that day, and the resultant 'end of day count' of unresolved incidents.

The data source consists of three coumns: 'ID' - an identifier, 'Incident Received Date' - the date the new incident was raised, and 'Resolution Date' - the date the incident was resolved. Once a incident is resolved, a date will be entered; if there is no date it means the incident is currently unresolved.

At the moment I have implemented the logic for this using DAX measures - but I have created one measure at a time for each value as above. E.g. One measure for Daily Date, one measure for 'Unresolved cases Start of day' etc. The measures also use relative dates; so currently we can see the last two days worth of data. But ideally it would be good for new dates to simply be added to the end of the table.

This is the desired result

Date	Unresolved cases start of day	New cases	cases resolved	Unresolved cases end of day
13/06/2022	224	57	1	280
14/06/2022	280	5	2	283
15/06/2022	283	etc	etc	etc

Measures in use

Is there a way to summarise this data more simply?

Thank you

[Anonymous · Accepted solution]

Hi @xr ,

1. If you want to append daily data to the table and the past data does not change, this is not possible, the expression is calculated dynamically every day.

2. If you want a dynamic table visual, please create these.

table.

Calendar = CALENDAR(MIN('CaseData'[Incident Received Date]),TODAY())

relationship.

measures.

Unresolved Cases End of day = 
CALCULATE (
    COUNTROWS ( 'CaseData' ),
    FILTER (
        ALL ( 'CaseData' ),
        'CaseData'[Resolution Date] = BLANK ()
            && 'CaseData'[Incident Received Date] <= MIN ( 'Calendar'[Date] )
    )
)

Cases Resolved Yesterday = 
CALCULATE (
    COUNTROWS ( 'CaseData' ),
    FILTER (
        ALL ( 'CaseData' ),
        'CaseData'[Resolution Date]
            = MAX ( 'Calendar'[Date] ) - 1
    )
)

New Cases Yesterday = 
CALCULATE (
    COUNTROWS ( 'CaseData' ),
    FILTER (
        ALL ( 'CaseData' ),
        'CaseData'[Incident Received Date]
            = MAX ( 'Calendar'[Date] ) - 1
    )
)

Unresloved Cases Start of day = 
[Unresolved Cases End of day] + [Cases Resolved Yesterday] - [New Cases Yesterday]

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[xr]

This is exactcly what I was looking for thank you! Am I correct in assuming that in the measure "Unresolved Cases End Of Day" the MIN funciton is used as a necessary but useless function to link the case data to the related calendar date?

Unresolved Cases End of day = 
CALCULATE (
    COUNTROWS ( 'CaseData' ),
    FILTER (
        ALL ( 'CaseData' ),
        'CaseData'[Resolution Date] = BLANK ()
            && 'CaseData'[Incident Received Date] <= MIN ( 'Calendar'[Date] )
    )
)

[Anonymous]

Hi @xr ,

Yes, in this case MIN ( 'Calendar'[Date] ) returns the visible filter for the 'Calendar'[Date] column in the current row.

For example, on the line with the date 6/18/2022, MIN ( 'Calendar'[Date] ) would return 6/18/2022.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data