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...
@measure
DAXMeasure
week over week
  • tamerj1's avatar
    tamerj1
    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's avatar
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's avatar
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' )
        )
    )