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:   Client Start End John Smith 01/06/2020 30/06/2020 Jane Smith 01/06/2020 30/06/...
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

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