How to compare changes by date

[deanbland]

Hi all, 

I have a dataset that looks like below: 

Issue ID	Export Date	Status	Due Date
I1	28/06/2021	Closed	31/12/2021
I2	28/06/2021	Open	31/12/2021
I3	28/06/2021	Open	31/12/2021
I4	28/06/2021	Open
I1	05/07/2021	Open	31/12/2021
I2	05/07/2021	Closed	31/12/2021
I3	05/07/2021	Open
I4	05/07/2021	Closed
I5	05/07/2021	Open	31/12/2021

What I am trying to do is compare the data set each week (defined by the 'Export Date' column) to show movements between the weeks. 

I want to know how many Issues have changed status, how many don't have Due Dates and how many new Issues we have compared to last week. 

From the dataset, this is the output I would expect: 

New - 1 (I5)

Closed - 2 (I2 & I4)

Open - 2 (I1 & I5)

No Due Date - 1 (I5) While there are two with no due dates, I am just looking to represent a change in this statue. So if an onld Issue changes to not having a due date, I would want to represent this, not the total number without one overall.  

I hope this makes sense, if any clarification is needed let me know! 

[Icey]

Hi @deanbland ,

Does the method I provide above work in your scenario?

Best Regards,

Icey

[Icey · Accepted solution]

Hi @deanbland ,

No Due Date - 1 (I5) While there are two with no due dates, I am just looking to represent a change in this statue. So if an onld Issue changes to not having a due date, I would want to represent this, not the total number without one overall.  

---

To my understand, this one is "I3", changed from "31/12/2021" to blank. Right?

Then try to create measures like so:

New = 
VAR ThisWeek_ =
    MAX ( 'Table'[Export Date] )
VAR LastWeek_ =
    CALCULATE ( MAX ( 'Table'[Export Date] ), 'Table'[Export Date] < ThisWeek_ )
VAR IssuesThisWeek_ =
    DISTINCT (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Export Date] = ThisWeek_ ),
            'Table'[Issue ID]
        )
    )
VAR IssuesLastWeek_ =
    DISTINCT (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Export Date] = LastWeek_ ),
            'Table'[Issue ID]
        )
    )
VAR NewIssues_ =
    EXCEPT ( IssuesThisWeek_, IssuesLastWeek_ )
RETURN
    COUNTROWS ( NewIssues_ )

Closed = 
VAR ThisWeek_ =
    MAX ( 'Table'[Export Date] )
VAR LastWeek_ =
    CALCULATE ( MAX ( 'Table'[Export Date] ), 'Table'[Export Date] < ThisWeek_ )
VAR IssuesThisWeek_ =
    DISTINCT (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Export Date] = ThisWeek_
                    && 'Table'[Status] = "Closed"
            ),
            'Table'[Issue ID]
        )
    )
VAR IssuesLastWeek_ =
    DISTINCT (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Export Date] = LastWeek_
                    && 'Table'[Status] <> "Closed"
            ),
            'Table'[Issue ID]
        )
    )
VAR ClosedIssues_ =
    INTERSECT ( IssuesThisWeek_, IssuesLastWeek_ )
RETURN
    COUNTROWS ( ClosedIssues_ )

Open = 
VAR ThisWeek_ =
    MAX ( 'Table'[Export Date] )
VAR LastWeek_ =
    CALCULATE ( MAX ( 'Table'[Export Date] ), 'Table'[Export Date] < ThisWeek_ )
VAR IssuesThisWeek_ =
    DISTINCT (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Export Date] = ThisWeek_
                    && 'Table'[Status] = "Open"
            ),
            'Table'[Issue ID]
        )
    )
VAR IssuesLastWeek_ =
    DISTINCT (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Export Date] = LastWeek_
                    && 'Table'[Status] <> "Open"
            ),
            'Table'[Issue ID]
        )
    )
VAR OpenIssues_ =
    INTERSECT ( IssuesThisWeek_, IssuesLastWeek_ )
RETURN
    COUNTROWS ( OpenIssues_ ) + [New]

No Due Date = 
VAR ThisWeek_ =
    MAX ( 'Table'[Export Date] )
VAR LastWeek_ =
    CALCULATE ( MAX ( 'Table'[Export Date] ), 'Table'[Export Date] < ThisWeek_ )
VAR IssuesThisWeek_ =
    DISTINCT (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Export Date] = ThisWeek_
                    && ISBLANK ( 'Table'[Due Date] )
            ),
            'Table'[Issue ID]
        )
    )
VAR IssuesLastWeek_ =
    DISTINCT (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Export Date] = LastWeek_
                    && NOT ( ISBLANK ( 'Table'[Due Date] ) )
            ),
            'Table'[Issue ID]
        )
    )
VAR NoDueDateIssues_ =
    INTERSECT ( IssuesThisWeek_, IssuesLastWeek_ )
RETURN
    COUNTROWS ( NoDueDateIssues_ )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.