Forum Discussion
Week over week measure
- 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' ) ) )
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' )
)
)
Hi tamerj1,
I am seeing the exact same result. Still the lowest value for the Year_Week_Numeric (red lines) for DaysOpen.
- tamerj11 year agoCommunity Champion
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' )
)
)