Week over week measure

[JC2022]

Hi,

I have created this "# Open tickets EOP" measure and it is working and looks like this below:

OpenTasksWoW =

VAR MinDate = MIN ( 'dim_date'[Date] )

VAR MaxDate = MAX ( 'dim_date'[Date] )

VAR Result =

    CALCULATE (

        COUNTROWS ( CurrentRow_tasks ),

        CurrentRow_tasks[taskCreatedDateDK] <= MaxDate,

        OR(

            CurrentRow_tasks[taskClosedDateDK] > MinDate,

            ISBLANK(CurrentRow_tasks[taskClosedDateDK])

        ),

        REMOVEFILTERS ( 'dim_date' )

    )

RETURN

    Result

# Open tickets EOP =

CALCULATE (

    [OpenTasksWoW],

    LASTDATE ( 'dim_date'[Date] )

)

But now I also want to calculate the days the ticket is open week over week. This measure is not working because it shows only a value for the first week a ticket is open.

DaysOpen =

VAR CreatedDate = MAX(CurrentRow_tasks[taskCreatedDateDK])

VAR ClosedDate = MAX(CurrentRow_tasks[taskClosedDateDK])

VAR LastDateInPeriod = LASTDATE('dim_date'[Date])

RETURN

    IF(

        ISBLANK(ClosedDate),

        DATEDIFF(CreatedDate, LastDateInPeriod, DAY),

        IF(

            ClosedDate <= LastDateInPeriod,

            DATEDIFF(CreatedDate, ClosedDate, DAY),

            DATEDIFF(CreatedDate, LastDateInPeriod, DAY)

        )

    )

I want a value for EVERY week a ticket is open. So for every end of period it should calculate the days it is open.
In this example: INCIDENT798633 should have value 11 for Year_Week_Numeric = 202506, value 18 for Year_Week_Numeric = 202507, value 25 for Year_Week_Numeric = 202508, etc.

[tamerj1 · Accepted solution]

@JC2022 
Aplogies again for the late response.
I hope the following fulfils your requirement.

DaysOpen = 
VAR MinDate =
    MIN ( 'dim_date'[Date] )
VAR MaxDate =
    MAX ( 'dim_date'[Date] )
RETURN
    AVERAGEX (
        CALCULATETABLE ( 
            VALUES ( CurrentRow_tasks[taskKey] ),
            CurrentRow_tasks[taskCreatedDateDK] <= MaxDate,
            OR (
                CurrentRow_tasks[BusinessClosingDateDK] > MinDate,
                ISBLANK ( CurrentRow_tasks[BusinessClosingDateDK] )
            ),
            REMOVEFILTERS ( 'dim_date' )
        ),
        CALCULATE (
            VAR CreatedDate =
                MAX ( CurrentRow_tasks[taskCreatedDateDK] )
            VAR ClosedDate =
                COALESCE ( MAX ( CurrentRow_tasks[BusinessClosingDateDK] ), MaxDate )
            RETURN
                DATEDIFF ( CreatedDate, MIN ( ClosedDate, MaxDate ), DAY ),
            CurrentRow_tasks[taskCreatedDateDK] <= MaxDate,
            OR (
                CurrentRow_tasks[BusinessClosingDateDK] > MinDate,
                ISBLANK ( CurrentRow_tasks[BusinessClosingDateDK] )
            ),
            REMOVEFILTERS ( 'dim_date' )
        )
    )

[JC2022]

@tamerj1  Very very close now. As we can see in your screenshot, for week 5 the matrix shows 27.5 (which is the correct value, but that's because there is a visual filter on [# Open Orders EOP] > 0), but it shows 22.69 in the line graph. This is due to the fact it is counting the lines with [# Open Orders EOP] is blank as well, while it should only take the [# Open Orders EOP] > 0 into account. See my screenshot. 
I think I have to change the "MinDate" at the end into "MaxDate" but then it is probably the [OpenTaskWoW] measure who needs to be adjusted so in only shows the >0 values.

[tamerj1]

Hi @JC2022 

DaysOpen = 
VAR MinDate =
    MIN ( 'dim_date'[Date] )
VAR MaxDate =
    MAX ( 'dim_date'[Date] )
RETURN
    AVERAGEX (
        FILTER ( 
            CALCULATETABLE ( 
                VALUES ( CurrentRow_tasks[taskKey] ),
                CurrentRow_tasks[taskCreatedDateDK] <= MaxDate,
                OR (
                    CurrentRow_tasks[BusinessClosingDateDK] > MinDate,
                    ISBLANK ( CurrentRow_tasks[BusinessClosingDateDK] )
                ),
                REMOVEFILTERS ( 'dim_date' )
            ),
            [# Open Orders EOP] > 0
        ),
        CALCULATE (
            VAR CreatedDate =
                MAX ( CurrentRow_tasks[taskCreatedDateDK] )
            VAR ClosedDate =
                COALESCE ( MAX ( CurrentRow_tasks[BusinessClosingDateDK] ), MaxDate )
            RETURN
                DATEDIFF ( CreatedDate, MIN ( ClosedDate, MaxDate ), DAY ),
            CurrentRow_tasks[taskCreatedDateDK] <= MaxDate,
            OR (
                CurrentRow_tasks[BusinessClosingDateDK] > MinDate,
                ISBLANK ( CurrentRow_tasks[BusinessClosingDateDK] )
            ),
            REMOVEFILTERS ( 'dim_date' )
        )
    )

[tamerj1]

Hi @JC2022 

please try

DaysOpen =
VAR MinDate =
MIN ( 'dim_date'[Date] )
VAR MaxDate =
MAX ( 'dim_date'[Date] )
RETURN
CALCULATE (
VAR CreatedDate =
MAX ( CurrentRow_tasks[taskCreatedDateDK] )
VAR ClosedDate =
COALESCE ( MAX ( CurrentRow_tasks[taskClosedDateDK] ), MaxDate )
RETURN
DATEDIFF ( CreatedDate, MIN ( ClosedDate, MaxDate ), DAY ),
CurrentRow_tasks[taskCreatedDateDK] <= MaxDate,
OR (
CurrentRow_tasks[taskClosedDateDK] > MinDate,
ISBLANK ( CurrentRow_tasks[taskClosedDateDK] )
),
REMOVEFILTERS ( 'dim_date' )
)

[JC2022]

Hi @tamerj1,
This works good for the individual tickets, but for a Year_Week_Numeric it looks like it is showing the lowest days open in that Year_Week_Numeric. I would like to see the average per Year_Week_Numeric. 
Thank you for your help! Very much appreciated.

[tamerj1]

Hi @JC2022 

sorry for the late response 

DaysOpen =
VAR MinDate =
MIN ( 'dim_date'[Date] )
VAR MaxDate =
MAX ( 'dim_date'[Date] )
RETURN
AVERAGEX (
VALUES ( dim_date[Year_Week_Numeric] ),
CALCULATE (
VAR CreatedDate =
MAX ( CurrentRow_tasks[taskCreatedDateDK] )
VAR ClosedDate =
COALESCE ( MAX ( CurrentRow_tasks[taskClosedDateDK] ), MaxDate )
RETURN
DATEDIFF ( CreatedDate, MIN ( ClosedDate, MaxDate ), DAY ),
CurrentRow_tasks[taskCreatedDateDK] <= MaxDate,
OR (
CurrentRow_tasks[taskClosedDateDK] > MinDate,
ISBLANK ( CurrentRow_tasks[taskClosedDateDK] )
),
REMOVEFILTERS ( 'dim_date' )
)
)

[JC2022]

Hi @tamerj1,
I am seeing the exact same result. Still the lowest value for the Year_Week_Numeric (red lines) for DaysOpen.

[tamerj1]

@JC2022 

My mistake!

DaysOpen =
VAR MinDate =
MIN ( 'dim_date'[Date] )
VAR MaxDate =
MAX ( 'dim_date'[Date] )
RETURN
AVERAGEX (
VALUES ( CurrentRow_tasks[TicketID] ),
CALCULATE (
VAR CreatedDate =
MAX ( CurrentRow_tasks[taskCreatedDateDK] )
VAR ClosedDate =
COALESCE ( MAX ( CurrentRow_tasks[taskClosedDateDK] ), MaxDate )
RETURN
DATEDIFF ( CreatedDate, MIN ( ClosedDate, MaxDate ), DAY ),
CurrentRow_tasks[taskCreatedDateDK] <= MaxDate,
OR (
CurrentRow_tasks[taskClosedDateDK] > MinDate,
ISBLANK ( CurrentRow_tasks[taskClosedDateDK] )
),
REMOVEFILTERS ( 'dim_date' )
)
)

[JC2022]

@tamerj1  it is still not working, but I have sent you a message.

[d_m_LNK]

For your LastDateInPeriod Variable, I'm curious what happens if you change it to MAX('dim_date'[Date) instead of LastDate.  It seems like it's only evaluating the max within that week context instead of the whole calendar table.  

[JC2022]

@d_m_LNK I tried but it doesn't change anything. Again it shows missing values.

[d_m_LNK]

On your variables for CreatedDate and ClosedDate, Change those to SelectedValue() instead of the MAX function as I am guessing you want to get the created an closed dates for those specific tickets and not the MAX created date in that column.

[JC2022]

No, this is not solving the problem. Thank you for trying to help. Is anyone else having the solution?