Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Progress to date table

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:

 

IDStatusChangedDate
A1Opened01-Mar-17
A2Closed07-Mar-17
B4Closed09-Mar-17
C3Opened09-Mar-17
A1Closed01-Apr-17
A1Issued01-May-17

 

Ideal Output:

DateOpenClosedIssued
Mar-17220
Apr-17130
May-17121

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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