Creating measure - days it took them to reach the goal but it s/b in 2 consecutive weeks

[MacyMolly]

Need your expertise on this one. 
I'm trying to create a measure that will calculate total days it took them to achieved the goal (the ones highlighted in green) but it should be in the first 2 consecutive weeks before they will be considered as passers. Like for example on the below dax table, looking at the first student named "Ampo", Ampo were able to achieved the goal in the frst 2 consecutive weeks in days 83 and 90 respectively. I only need the tenure in days of the first week they achieved the goal; therefore, the output s/b 83 days just for Ampo. Again, the student should get the passing score for at least two consecutive weeks.

Output other students:

Cabarrubias:  days 69 and 76. output s/b 69

Cuico: days 76 and 83. output s/b 76

Your help is greatly appreciated.

[Icey · Accepted solution]

Hi @MacyMolly ,

Please check:

Method 1: Don't use week number.

1. Create measures like so:

Conditional formatting Measure =
IF ( AVERAGE ( 'Table'[Value] ) >= 72, 1, 0 )

Day Diff =
VAR NextDays_ =
    IF (
        [Conditional formatting Measure] = 1,
        CALCULATE (
            MIN ( 'Table'[Days] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Last Name] = MAX ( 'Table'[Last Name] )
                    && 'Table'[Days] > SELECTEDVALUE ( 'Table'[Days] )
                    && [Conditional formatting Measure] = 1
            )
        )
    )
VAR DayDiff_ =
    NextDays_ - MAX ( 'Table'[Days] )
RETURN
    IF ( DayDiff_ = 7, 1 )

output =
CALCULATE (
    MIN ( 'Table'[Days] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Last Name] = MAX ( 'Table'[Last Name] )
            && [Day Diff] = 1
    )
)

2. Create visuals.

Method 2: Use week number.

1. Create a Date table with "Day of Year".

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
    "WeekNum", WEEKNUM ( [Date], 1 )
)

Day of Year =
DATEDIFF ( STARTOFYEAR ( 'Date'[Date] ), 'Date'[Date], DAY ) + 1

2. Create relationship between "Date" and "Table".

3. Create WeekNum column in "Table".

WeekNum =
RELATED ( 'Date'[WeekNum] )

4. Create measures.

Conditional formatting Measure =
IF ( AVERAGE ( 'Table'[Value] ) >= 72, 1, 0 )

Week Diff =
VAR NextWeekNum_ =
    IF (
        [Conditional formatting Measure] = 1,
        CALCULATE (
            MIN ( 'Table'[WeekNum] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Last Name] = MAX ( 'Table'[Last Name] )
                    && 'Table'[WeekNum] > SELECTEDVALUE ( 'Table'[WeekNum] )
                    && [Conditional formatting Measure] = 1
            )
        )
    )
VAR WeekDiff_ =
    NextWeekNum_ - MAX ( 'Table'[WeekNum] )
RETURN
    IF ( WeekDiff_ = 1, 1 )

output =
CALCULATE (
    MIN ( 'Table'[Days] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Last Name] = MAX ( 'Table'[Last Name] )
            && [Week Diff] = 1
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MacyMolly]

@Anonymous , i'm sorry. One more thing, if i'm not going to include days 62 and just focus on days 69 and onwards. what would be the measure? Thank you.

[Icey]

Hi @MacyMolly ,

Just try this:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MacyMolly]

Good day @Icey  Wow! This is so Amazing! Both methods works perfectly! You just saved my ass! Thank you and Kudos!!!