Power BI reports with Missing dates

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.