Accumolate exceeding cases

[Noak]

Hey everyone,

I have new mission and need your help .

My data based on open cases that divided to seveirties(1 high - 3 low), the CTO defindes cases SLA eg: if case defined as high the resolving time of that specific case is no longer than 24 days. It's a binaric situation in case we exceeded that period IsExceeding=1. in case we didn't it will be 0.

I need to present accumolate exceeding cases per month - stacked column chart that presents 3 layers of the severities.

Here's what I did,

 

As you can see what happened here is that if in total i have 102 exceeding cases the chart shows how much belongs to each month and doesnt shows me accumolate cases.

I mean I need to see in fabruary 2017 overall view of how much cases is exceeding until 31.2.17 - is should sum all open exceeding cases for last X months and shows how much did it collect until the 31.2 and divide it to severity type (high-low).

here's my data:

 

[Eric_Zhang]

@Noak

Your requirement is not quite clear for me. Based on my understanding, it looks that you're looking for a measure like 

Acc exceeding cases =
COUNTROWS (
    FILTER (
        yourTable,
        yourTable[createon] <= MAX ( yourTable[createon] )
            && yourTable[isExceed] = 1
    )
)

By the way, could you please post the sample data in plain text, the picture doesn't help too much.

[Noak]

Hi @Eric_Zhang, 

I already used this messaure.

It doesnt answer my dashboard needs, lets say today we have 100 exceeding cases that are still open.

I want to see the trend in monthly view,

current result = power bi takes 100 cases spred them by the creation month and give me a view of how many exceeding case from january,feb,march.... I dont care how much from each month I care to see the trend over time.

heres a glanss of the data:

createdon	Acc exceeding cases	severitycodename
29/02/2016 15:03	1	Severity 2 - Medium
15/03/2016 08:56	1	Severity 2 - Medium
18/03/2016 14:16	1	Severity 3 - Low
18/03/2016 14:47	1	Severity 2 - Medium
14/04/2016 12:50	1	Severity 2 - Medium
15/04/2016 08:25	1	Severity 2 - Medium
22/04/2016 08:51	1	Severity 2 - Medium
06/05/2016 14:26	1	Severity 2 - Medium
09/05/2016 15:20	1	Severity 2 - Medium
16/05/2016 10:55	1	Severity 2 - Medium
16/05/2016 10:59	1	Severity 3 - Low
16/05/2016 11:05	1	Severity 2 - Medium
17/05/2016 13:27	1	Severity 2 - Medium
30/05/2016 05:04	1	Severity 2 - Medium
30/05/2016 05:37	1	Severity 2 - Medium
02/06/2016 13:16	1	Severity 2 - Medium
03/06/2016 14:28	1	Severity 2 - Medium
06/06/2016 15:42	1	Severity 2 - Medium
07/06/2016 08:33	1	Severity 2 - Medium
08/06/2016 12:29	1	Severity 2 - Medium
08/06/2016 12:35	1	Severity 2 - Medium
09/06/2016 12:38	1	Severity 2 - Medium
10/06/2016 07:43	1	Severity 2 - Medium
14/06/2016 11:43	1	Severity 2 - Medium
15/06/2016 09:33	1	Severity 2 - Medium
21/06/2016 07:39	1	Severity 2 - Medium
21/06/2016 14:19	1	Severity 1 - High
24/06/2016 10:31	1	Severity 2 - Medium
27/06/2016 10:23	1	Severity 2 - Medium
27/06/2016 10:29	1	Severity 2 - Medium
29/06/2016 14:57	1	Severity 2 - Medium
29/06/2016 14:59	1	Severity 1 - High
30/06/2016 07:31	1	Severity 1 - High
30/06/2016 10:46	1	Severity 1 - High
30/06/2016 18:33	1	Severity 2 - Medium
30/06/2016 19:50	1	Severity 2 - Medium
04/07/2016 09:46	1	Severity 2 - Medium
05/07/2016 09:26	1	Severity 2 - Medium
08/07/2016 09:11	1	Severity 1 - High
08/07/2016 09:28	1	Severity 2 - Medium
11/07/2016 14:39	1	Severity 2 - Medium
11/07/2016 14:44	1	Severity 3 - Low
19/07/2016 18:11	1	Severity 1 - High
20/07/2016 19:25	1	Severity 2 - Medium
21/07/2016 07:10	1	Severity 2 - Medium
22/07/2016 12:50	1	Severity 2 - Medium
22/07/2016 13:21	2	Severity 2 - Medium
23/07/2016 16:10	1	Severity 2 - Medium
27/07/2016 08:25	1	Severity 2 - Medium
27/07/2016 09:03	1	Severity 2 - Medium
27/07/2016 15:11	1	Severity 2 - Medium
29/07/2016 13:07	1	Severity 2 - Medium
29/07/2016 13:51	1	Severity 2 - Medium
29/07/2016 14:00	1	Severity 2 - Medium
29/07/2016 14:08	1	Severity 2 - Medium
01/08/2016 11:19	1	Severity 2 - Medium
01/08/2016 11:33	1	Severity 2 - Medium
02/08/2016 13:29	1	Severity 2 - Medium
03/08/2016 21:30	1	Severity 2 - Medium
08/08/2016 21:22	1	Severity 2 - Medium
10/08/2016 15:39	1	Severity 2 - Medium
17/08/2016 20:39	1	Severity 2 - Medium
22/08/2016 13:48	1	Severity 2 - Medium
26/08/2016 19:47	1	Severity 2 - Medium
09/09/2016 10:13	1	Severity 2 - Medium
13/09/2016 10:54	1	Severity 3 - Low
14/09/2016 09:23	1	Severity 1 - High
19/09/2016 11:09	1	Severity 2 - Medium
19/09/2016 19:23	1	Severity 2 - Medium
21/09/2016 19:21	1	Severity 2 - Medium
22/09/2016 09:42	1	Severity 2 - Medium
23/09/2016 09:25	1	Severity 2 - Medium
27/09/2016 15:31	1	Severity 2 - Medium

[Eric_Zhang]

---

@Noak wrote:

Hi @Eric_Zhang, 

I already used this messaure.

It doesnt answer my dashboard needs, lets say today we have 100 exceeding cases that are still open.

I want to see the trend in monthly view,

current result = power bi takes 100 cases spred them by the creation month and give me a view of how many exceeding case from january,feb,march.... I dont care how much from each month I care to see the trend over time.

---

What do you mean a trend then? May be a increasing or dropping percentage of each month?

[Noak]

---

@v-lvzhan-msft wrote:

---

 What do you mean a trend then? May be a increasing or dropping percentage of each month?

---

I mean if increasing @Eric_Zhang. do you have any solution for that?