Forum Discussion
JC2022
Helper III
1 year agoWeek 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] ) VA...
- 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' ) ) )
tamerj1
Community Champion
1 year agoMy 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' )
)
)