cancel
Showing results for
Did you mean:
New Member

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

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

Moderator

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

@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,

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,385)