topic Re: Week over week measure in DAX Commands and Tips

Week over week measure

JC2022 — Fri, 21 Mar 2025 08:13:14 GMT

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.

Re: Week over week measure

d_m_LNK — Thu, 20 Mar 2025 18:03:58 GMT

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.

Re: Week over week measure

JC2022 — Fri, 21 Mar 2025 07:37:00 GMT

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

Re: Week over week measure

d_m_LNK — Fri, 21 Mar 2025 13:28:03 GMT

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.

Re: Week over week measure

JC2022 — Fri, 21 Mar 2025 15:13:39 GMT

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

Re: Week over week measure

tamerj1 — Fri, 21 Mar 2025 17:35:18 GMT

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

Re: Week over week measure

JC2022 — Sat, 22 Mar 2025 11:53:07 GMT

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.

Re: Week over week measure

tamerj1 — Tue, 25 Mar 2025 19:14:22 GMT

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

Re: Week over week measure

JC2022 — Wed, 26 Mar 2025 08:03:33 GMT

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

Re: Week over week measure

tamerj1 — Wed, 26 Mar 2025 14:04:20 GMT

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

Re: Week over week measure

JC2022 — Thu, 27 Mar 2025 15:47:40 GMT

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

Re: Week over week measure

tamerj1 — Sat, 29 Mar 2025 10:19:52 GMT

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

Re: Week over week measure

JC2022 — Mon, 31 Mar 2025 07:43:07 GMT

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.

Re: Week over week measure

tamerj1 — Wed, 02 Apr 2025 04:08:45 GMT

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