Helper III

1 year ago

Solved

Week over week measure

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.

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' )
        )
    )

13 Replies

d_m_LNK
Super User
1 year ago
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
  Helper III
  1 year ago
  d_m_LNK I tried but it doesn't change anything. Again it shows missing values.
  - d_m_LNK
    Super User
    1 year ago
    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.
tamerj1
Community Champion
1 year ago
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
  Helper III
  1 year ago
  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
    Community Champion
    1 year ago
    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' )
    )
    )

tamerj1

Community Champion

1 year ago

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

Helper III

1 year ago

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

Community Champion

1 year ago

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' )
        )
    )

Forum Discussion

Week over week measure

13 Replies

Related content

Curren week measure

Measure displaying last week sum

This week date measure

ISO week

Week over Week Diff