Forum Discussion

redhughes's avatar
redhughes
Helper II
6 years ago
Solved

Detecting gaps

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

 

ClientStartEnd
John Smith01/06/202030/06/2020
Jane Smith01/06/202030/06/2020
John Smith01/07/202031/07/2020
Jane Smith10/07/202031/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? 🙂

Need Help
  • tex628's avatar
    tex628
    6 years ago

    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's avatar
    v-deddai1-msft
    6 years ago

    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

6 Replies

  • tex628's avatar
    tex628
    Community Champion
    6 years ago

    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



     

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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")

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.  

     

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    6 years ago

    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