Calculating total cases created per day, how many were closed and by who, how many stayed open.

[92470am]

Hi,

I have to calculate the total # of cases created per day. Of those cases, how many were closed, who closed them and also how many of the original cases were still open at the end of the day. I want a daily look, weekly look, and monthly look.

Thank you,

Audra

[v-sihou-msft]

@92470am

In this scenario, you need to have a full calendar table (connecting your Case table on date column) with all different date parts columns (Year, Quarter, Month, Day and Week) so that data can aggregate on corresponding level. You also need a column for tagging a case is closed or not. Then you just need to create a measure to calculate daily created cases.

Daily Created Cases Volume =
CALCULATE ( COUNTA ( Case[Case] ), ALLEXCEPT ( Case, Case[CaseCreatedDate] ) )

To calculate daily closed cases, just filter the table context with the [IsClosed] column.

Daily Closed Cases Volume =
CALCULATE (
    COUNTA ( Case[Case] ),
    ALLEXCEPT ( Case, Case[CaseCreatedDate] ),
    FILTER ( Case, Case[IsClosed] = 1 )
)

Regards,