Progress to date table

[Anonymous]

Hello,

I am struggling to produce what I would refer to as a progress to date table.

Basically at each month the table should display a count of the latest status's.

So in the example below ID A2 has been closed in March-17 but should still contribute to the April and May count of closed until it is changed to Issued. Only then does it stop counting towards Closed but contributes to the Issued Status instead.

ID A1 is an example of a changing ID through the months.

Data Input:

ID	Status	ChangedDate
A1	Opened	01-Mar-17
A2	Closed	07-Mar-17
B4	Closed	09-Mar-17
C3	Opened	09-Mar-17
A1	Closed	01-Apr-17
A1	Issued	01-May-17

Ideal Output:

Date	Open	Closed	Issued
Mar-17	2	2	0
Apr-17	1	3	0
May-17	1	2	1

Summary
Row 1) Open = A1, C3; Closed = A2, B4
Row 2) Open = C3; Closed = A2, B4, A1
Row 3) Open = C3; Closed = A2, B4; Issued = A1

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Firstly, each ID that is closed should have open date in your Table, right? If so, create the following columns in your table.

OpenDate = CALCULATE(MIN(Table1[ChangedDate]),FILTER(Table1,Table1[ID]=EARLIER(Table1[ID])))
CloseDate = IF(Table1[Status]="Closed",Table1[ChangedDate],BLANK())
IssueDate = IF(Table1[Status]="Issued",Table1[ChangedDate],BLANK())

NewCloseDate = 
VAR vid = Table1[ID]
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Table1[CloseDate], 1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[ID] = vid          
               && NOT(ISBLANK( Table1[CloseDate] )
        )
    ))

NewIssueDate = 
VAR vid = Table1[ID]
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Table1[IssueDate], 1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[ID] = vid  
                && NOT(ISBLANK( Table1[IssueDate])
        )
    ))

Secondly, create a new table using DAX below.

Test = SUMMARIZE(Table1,Table1[ID],Table1[OpenDate],Table1[NewCloseDate],Table1[NewIssueDate])

Thirdly, calculate opened/closed/issued tickets following the instructions in this similar thread.

Regards,
Lydia