Number of opened and closed issues per week + cumulative count of the opened issues in a chart

[Anonymous]

 Hello folks,

I would like to display on a "line and clustered chart" the number of tickets opened and closed per year week.
First I have my data table that looks like this :
 

To do that, I followed the answer given in this topic LINK 

I created a calendar table and I linked it to the "creation date" :

To finish, I created two columns in the table to put a 0 or 1 if closed or opened :

Opened = IF(ISBLANK('Data Struct'[Date closure]);1;0)

Closed = IF(ISBLANK('Data Struct'[Date closure]);0;1)

The problem is when I display that on a "line and clustered column chart" I don't get the real number of issues that where closed per week but the number of issues opened and closed the same week ( ex : last week I get 6 but I should get 50):

The second question is how can we make a cumulative count of the opened issues ?

Thank you in advance !

[sturlaws]

Hi @Anonymous ,

this happens because you connect to [Created date]. You need to add a new relationship between your two tables, between Calendar[Date] and Data[Closed date]. This second relationship will be inactive. In order to make use of it, you have to use the USERELATIONSHIP-function inside a measure, something like this

measureClosed =
CALCULATE (
    SUM ( Data[Closed] );
    USERELATIONSHIP ( Data[Date Closure]; Calendar[Date] )
)

As for your cumulative count, it will depend a little on you Calendar-table, but in general it should look something like this

cumulative open =
CALCULATE (
    SUM ( data[open] );
    FILTER (
        ALL ( Calendar );
        Calendar[Week] <= MIN ( Calendar[Week] )
            && Calendar[Year] = MIN ( Calendar[Year] )
    )
)

Cheers,

Sturla