Solved: Report If data missing for more than 3 concecutive...

karti1507 · ‎08-28-2024

I have the below data, where in I have to report if the data is missing for more than 3 days. for example City - Jaurez Compaby - BPI Site - JPP data missing after 8/7 for more than 3 days. Some how I need to flag them! Any help is appreciated!! Thanks.

Anonymous · ‎08-28-2024

Hi @karti1507 ,

I suggest you try this code to create a measure.

Measure = 
VAR _PREVIOUSDATE =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[City] = MAX ( 'Table'[City] )
                && 'Table'[Company] = MAX ( 'Table'[Company] )
                && 'Table'[Site] = MAX ( 'Table'[Site] )
                && 'Table'[Date Provided] < MAX ( 'Table'[Date Provided] )
        ),
        'Table'[Date Provided]
    )
VAR _FUTUREDATE =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[City] = MAX ( 'Table'[City] )
                && 'Table'[Company] = MAX ( 'Table'[Company] )
                && 'Table'[Site] = MAX ( 'Table'[Site] )
                && 'Table'[Date Provided] > MAX ( 'Table'[Date Provided] )
        ),
        'Table'[Date Provided]
    )
VAR _DAYDIFF1 =
    DATEDIFF ( _PREVIOUSDATE, MAX ( 'Table'[Date Provided] ), DAY )
VAR _DAYDIFF2 =
    DATEDIFF ( MAX ( 'Table'[Date Provided] ), _FUTUREDATE, DAY )
RETURN
    IF ( _DAYDIFF1 > 3 || _DAYDIFF2 > 3, "Red" )

Then set the conditional formatting for [Date Provided] column.

Result is as below.

Best Regards,
Rico Zhou

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

View solution in original post

Anonymous · ‎08-28-2024