Detecting gaps

[redhughes]

We have a database of communication forms from our clients that holds the forms' start and end dates:

Client	Start	End
John Smith	01/06/2020	30/06/2020
Jane Smith	01/06/2020	30/06/2020
John Smith	01/07/2020	31/07/2020
Jane Smith	10/07/2020	31/07/2020

We're looking for a way of reporting on gaps in documentations, i.e. a report that would somehow flag that Jane Smith hasn't had a communication form covering the period of 01/07/2020-09/07/2020. Has anyone one got any brilliant ideas, please? 🙂

[tex628 · Accepted solution]

This column returns the gap between the current row and the most recent communication of the same client.  

Column = 
VAR Name_ = Query1[Client]
VAR End_ = Query1[End]
VAR Start_ = Query1[Start]
VAR Gap = Start_ - CALCULATE(MAX(Query1[End]) , ALL(Query1) , Query1[End] < Start_ , Query1[Client] = Name_) -1
Return 
Gap

Br,
J

[v-deddai1-msft · Accepted solution]

Hi @redhughes ,

Just use the measure below:

Measure =
VAR start_ =
    MAX ( 'Table'[Start] )
VAR lastend_ =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Client] = MAX ( 'Table'[Client] )
                && 'Table'[End] <= start_
        ),
        'Table'[End]
    )
RETURN
    IF (
        DATEDIFF ( lastend_, start_, DAY ) > 1,
        lastend_ + 1 & "-" & start_ - 1 & "gap",
        "No Gap"
    )

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

Best Regards,

Dedmon Dai

[tex628 · Accepted solution]

This column returns the gap between the current row and the most recent communication of the same client.  

Column = 
VAR Name_ = Query1[Client]
VAR End_ = Query1[End]
VAR Start_ = Query1[Start]
VAR Gap = Start_ - CALCULATE(MAX(Query1[End]) , ALL(Query1) , Query1[End] < Start_ , Query1[Client] = Name_) -1
Return 
Gap

Br,
J

[Greg_Deckler]

@redhughes - Should be able to get there using Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[Fowmy]

@redhughes 

In Power Query, paste below code in a blank query in the Advanced editor:

You can download the file: HERE

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTIw1Dcw0zcyMDIAcowN4JxYHaDCxLxU4hSim2gOV4jgYJhoaIBDYSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Start = _t, End = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start", type date}, {"End", type date}}, "en-GB"),
    Step1 = Table.TransformColumns(#"Changed Type with Locale",{{"Client", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(Step1, {"Client"}, {{"_Min", each List.Min([Start]), type nullable date}, {"_Max", each List.Max([End]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each { Number.From ([_Min]).. Number.From ([_Max])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    Due = Table.RemoveColumns(#"Changed Type",{"_Min", "_Max"}),
    Custom1 = Step1,
    #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each { Number.From ([Start]).. Number.From ([End])}),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Custom", type date}}),
    Actual = Table.RemoveColumns(#"Changed Type1",{"Start", "End"}),
    #"Merged Queries" = Table.NestedJoin(Due, {"Client", "Custom"}, Actual, {"Client", "Custom"}, "Actual", JoinKind.LeftOuter),
    #"Expanded Actual" = Table.ExpandTableColumn(#"Merged Queries", "Actual", {"Custom"}, {"Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Actual", each ([Custom.1] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Missing Dates"}})
in
    #"Renamed Columns"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

You could add an additional column using Datediff 

Column = DATEDIFF('Table (2)'[Start],'Table (2)'[End],DAY)

Then use the conditional format option on column colours.  

[amitchandak]

@redhughes , Check if this can help in getting the GAP

if(datediff(maxx(filter(Table, [Client]=earlier([Client]) && [Start]<earlier([Start])),[End]),[Start],day) >1, "Gap","No Gap")