Report If data missing for more than 3 concecutive dates

[karti1507]

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 · Accepted solution]

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.

[karti1507]

What if I want to list the dates which are missing 

Result should be 

City	Company	Site	Dates Missing
Juarez	BPI	JPP	8/8/2024
Juarez	BPI	JPP	8/9/2024
Juarez	BPI	JPP	8/10/2024
Juarez	Hopkins	Hopkins Manufacturing	8/8/2024
Juarez	Hopkins	Hopkins Manufacturing	8/9/2024
Juarez	Hopkins	Hopkins Manufacturing	8/10/2024
Juarez	Hopkins	Hopkins Manufacturing	8/11/2024
Juarez	Trico	Planta 1	8/7/2024
Juarez	Trico	Planta 1	8/8/2024
Juarez	Trico	Planta 1	8/9/2024