Cumulative Distinctcount for selected Date and Dimensions

[Lourini90]

I have that following Table:

It represents cases on wich a certain Team is working on over the Time until the case is closed.

And there is also a Date Table over column Date.

I would like to cumulative count the open cases until the selected date.

So I used this measure:

CountOpen =  
VAR CurrentDate = MAX('Date'[Date])
VAR Closed =
CALCULATE(
    DISTINCTCOUNT(Tabelle1[case]),
    ALL('Date'),'Date'[Date]<=CurrentDate,Tabelle1[Status_Open]="0")
VAR OpenAll =
CALCULATE(
    DISTINCTCOUNT(Tabelle1[case]),
    ALL('Date'),'Date'[Date]<=CurrentDate,Tabelle1[Status_Open]="1")
RETURN OpenAll-Closed

And it works for the overall view. But for the view within the Dimension CurrentTeam it's not correct:

It should be:

a = 0

b = 1

c = 0

Hope somebody can help me with that.

Thanks a lot!

[colacan]

Hi Lourini90, 

Your measure conducts DISTINCTCOUNT over "Case" which means there are only 2 distinct cases: 1 and 3.

And it again gets seperated by the filter of Calculate: 

    Tabelle1[Status_Open]="0" and 

    Tabelle1[Status_Open]="1"

That means, whatever the context is, the maximum value of "Oepn_All" or "Closed" cannot be bigger than 4:

Case1 & Tabelle1[Status_Open]="0"

Case1 & Tabelle1[Status_Open]="1"

Case3 & Tabelle1[Status_Open]="0"

Case3 & Tabelle1[Status_Open]="1"

==> Total 4 distinct cases.

If you understood, you would understand why the team "a" has the value of 2.

It's becuase the team "a" has 2 distinct cases as below. (you can easily check it by filtering your table with team "a")

Case1 & Tabelle1[Status_Open]="1"   

Case3 & Tabelle1[Status_Open]="1" (there 2 cases like this but number of distinct case is 1)

This is why your table shows 2 for team "a"

I hope this helps you.