Power BI reports with Missing dates

[karti1507]

I have the below file as input and have to create an o/p like the "Expected Report output" , Current day 9/4/2024 . If the data missing for more 3 consecutive days, or of the data is not received for more than 3 days considering the current day as latest date. I listed one of the company/city/site/KPI as example here.

[karti1507]

I have to find the missing data for each set of City Site Company,  Here is sample data from my input table 

City	Site	Company	KPI	Date Occurred
Juarez	Planta 2	Trico	Incident	8/1/2024 0:00
Juarez	Planta 2	Trico	Incident	8/2/2024 0:00
Juarez	Planta 2	Trico	Patrols & Security Checks	8/1/2024 0:00
Matamoros	Plant 5	Trico	Guard Schedule	8/1/2024 0:00
Matamoros	Plant 5	Trico	Guard Schedule	8/2/2024 0:00
Matamoros	Plant 5	Trico	Guard Schedule	8/3/2024 0:00
Matamoros	Plant 5	Trico	Patrols & Security Checks	8/1/2024 0:00
Matamoros	Plant 5	Trico	Patrols & Security Checks	8/2/2024 0:00
Matamoros	Plant 5	Trico	Patrols & Security Checks	8/3/2024 0:00
Matamoros	Plant 52	Tridonex	Guard Schedule	8/1/2024 0:00
Matamoros	Plant 52	Tridonex	Guard Schedule	8/2/2024 0:00
Matamoros	Plant 52	Tridonex	Guard Schedule	8/3/2024 0:00
Matamoros	Plant 52	Tridonex	Patrols & Security Checks	8/1/2024 0:00
Matamoros	Plant 52	Tridonex	Patrols & Security Checks	8/2/2024 0:00
Matamoros	Plant 52	Tridonex	Patrols & Security Checks	8/3/2024 0:00
Matamoros	Plant 55	Trico	Guard Schedule	8/1/2024 0:00
Matamoros	Plant 55	Trico	Guard Schedule	8/2/2024 0:00
Matamoros	Plant 55	Trico	Guard Schedule	8/3/2024 0:00
Matamoros	Plant 55	Trico	Patrols & Security Checks	8/1/2024 0:00
Matamoros	Plant 55	Trico	Patrols & Security Checks	8/2/2024 0:00
Matamoros	Plant 55	Trico	Patrols & Security Checks	8/3/2024 0:00

[Anonymous · Accepted solution]

Hi,@karti1507 

Regarding the issue you raised, my solution is as follows: 

1.First, I copied the original table main:

2.Second, use the following m-language:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZPfC8IgEMf/FfF5kNkPotcFUREN1tvqQfRg0tJwDqq/vgsKIoLN5ot3KPfxvp7foqCp9jea0Fx7wJDa80WY58YmW+G6EB7ITsrGOVD0mBR03QgHdzzKKmG8IBzTvdPSYlwZqRUYj+lsMBxwxseEzRkLK+QBhZnwzlY1OTSM8SnJATtFQSQtQZ7qn31shRdn62z9JpLJB3CJlymSyxJUU0EcwLegYMAoCBD9TToAwyR2AHaQ/PoHyhq4/j25dkgHae2QcDlxphgODZcbZ5r9Tdjfhf1tGN+H8Y3Yx4nHBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"City", type text}, {"Site", type text}, {"Company", type text}, {"KPI", type text}, {"Date Occurred", type datetime}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Date Occurred", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"City", "Site", "Company", "KPI"}, {{"start date", each List.Min([#"Date Occurred"]), type nullable date}, {"end date", each Date.FromText("2024-08-05") ,type nullable date}, {"all data", each Table.Sort(_,{"Date Occurred", Order.Ascending})}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Dates([start date],Duration.Days([end date]-[start date])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"start date", "end date", "all data"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"City", "Site", "Company", "KPI", "Custom"}, main, {"City", "Site", "Company", "KPI", "Date Occurred"}, "main", JoinKind.LeftAnti),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{"main"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Custom", "Date"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

3.Next, create a group by using the following calculated columns:

log =
VAR _last =
    MAXX (
        FILTER (
            ALL ( 'missingdate' ),
            'missingdate'[City] = EARLIER ( 'missingdate'[City] )
                && 'missingdate'[Company] = EARLIER ( 'missingdate'[Company] )
                && 'missingdate'[Site] = EARLIER ( 'missingdate'[Site] )
                && 'missingdate'[KPI] = EARLIER ( 'missingdate'[KPI] )
                && 'missingdate'[Date] < EARLIER ( 'missingdate'[Date] )
        ),
        'missingdate'[Date]
    )
VAR _lastday = 'missingdate'[Date] - 1
RETURN
    IF ( _last = _lastday, 1, 0 )

group =
VAR CurrentIndex1 = [Index]
VAR PreviousIndex1 =
    CALCULATE (
        MAX ( 'missingdate'[Index] ),
        FILTER (
            'missingdate',
            'missingdate'[Index] <= CurrentIndex1
                && 'missingdate'[log] = 0
        )
    )
RETURN
    PreviousIndex1

4.Then, the final result is generated using a calculation table like this:

result =
FILTER (
    ALLEXCEPT ( 'missingdate', 'missingdate'[log], 'missingdate'[Index] ),
    CALCULATE (
        COUNTROWS ( 'missingdate' ),
        FILTER (
            'missingdate',
            'missingdate'[group] = EARLIER ( 'missingdate'[group] )
        )
    ) > 3
)

5.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Anonymous]

Hi, @karti1507 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Second, I created the following calculation table, and used the following calculation column to get the grouping result of the missing time:

MissingDates =
ADDCOLUMNS (
    EXCEPT (
        CALENDAR ( MIN ( 'infile'[Date Occurred] ), TODAY () ),
        SELECTCOLUMNS ( 'infile', 'infile'[Date Occurred] )
    ),
    "index",
        RANKX (
            EXCEPT (
                CALENDAR ( MIN ( 'infile'[Date Occurred] ), TODAY () ),
                SELECTCOLUMNS ( 'infile', 'infile'[Date Occurred] )
            ),
            [Date],
            ,
            ASC,
            DENSE
        )
)

IsConsecutive = 
VAR currentday1='MissingDates'[Date]
VAR predate=CALCULATE(MAX('MissingDates'[Date]),FILTER('MissingDates','MissingDates'[Date]<currentday1))
RETURN  
IF(predate+1=currentday1,1,0)

group = 
VAR CurrentIndex1 = [Index]
VAR PreviousIndex1= CALCULATE(MAX('MissingDates'[index]), FILTER('MissingDates', 'MissingDates'[index]<=CurrentIndex1 && 'MissingDates'[IsConsecutive]=0))
RETURN PreviousIndex1

3.Next, the final result is obtained using the following calculation table:

result = 
DISTINCT (
    CROSSJOIN (
        SELECTCOLUMNS (
            FILTER (
                'MissingDates',
                CALCULATE (
                    COUNTROWS ( 'MissingDates' ),
                    FILTER (
                        'MissingDates',
                        'MissingDates'[group] = EARLIER ( 'MissingDates'[group] )
                    )
                ) > 3
            ),
            'MissingDates'[Date]
        ),
        ALLEXCEPT ( 'infile', 'infile'[Date Occurred] )
    )
)

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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