Forum Discussion

JC2022's avatar
JC2022
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] ) VA...
  • tamerj1's avatar
    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' )
            )
        )