Pulling usernames that appear 3 or more times within a certain time period

[Anonymous]

Hi,

I have three columns, Username/Test Date/Fail(in this scenario everyone included in fail has failed atleast once). I need to find all users that have failed a test 3 or more times within 180 days (6 months) of failing the first test.

Username             Test Date          Fail

Joe Bloggs          1/08/2019            1

John Peter          1/08/2019            1

Joe Bloggs         02/10/2019           1

John Peter         02/10/2019           1

Joe Bloggs        20/12/2019            1

Joe Bloggs         02/01/2020           1

John Peter         04/02/2020            1

From the above scenario Joe Bloggs Should be returned as he had 3 or more failures in 180 days(6 months) but John peter wont be returned as he only had 2 failures in the 180 days. Any help is appreciated.

Thanks

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVXDKyU9PL1bSUTLUt9A3MjC0BDGVYnVAshl5CgGpJalF2GWR9BrpGxrg0YxNGlm3gb6hEX7TgbJGBtgNN9E3QpKNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Username = _t, #"Test Date" = _t, Fail = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Username", type text}, {"Test Date", type date}, {"Fail", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Username"}, {{"Temp", each _, type table [Username=nullable text, Test Date=nullable date, Fail=nullable number]}}),
    //Function Start
    fxProcess = (Tbl)=>
        let
            #"Filtered Rows" = Table.SelectRows(Tbl, each ([Test Date] <=Date.AddDays(List.Min(Tbl[Test Date]),180))),
            CountRows = Table.RowCount(#"Filtered Rows")
        in
            CountRows,
    //Function End
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Custom] >= 3)
in
    #"Filtered Rows"

[Anonymous]

Hi Vijay,

This works to an extent as to what Ive shown in the previous example however on investigation of the results there are some users not included in the output that should be. In these cases the users have failed the first test but then they dont fail one for another 6 months. They then fail 3 in a row. It seems to be that the code misses these as it isnt a rolling time. Please see the example to make it clearer (I have added in a first name and surname column to make the output clearer).

Username          Firstname         Surname          Test Date              Fail

Joe Bloggs           Joe                   Bloggs           10/02/2021             1

Joe Bloggs           Joe                   Bloggs           25/09/2021             1

Joe Bloggs           Joe                   Bloggs           02/02/2022             1

Joe Bloggs           Joe                   Bloggs           23/02/2022            1

In the above case Joe bloggs wont be returned as there is longer than 6 months between the first fail and the next fail, but should be included as he fails 3 times within 180 days looking at the 2nd,3rd and 4th fail. Just as an added question is it then possible to return the most recent fail date alongside the username, like below.

Username        Firstname         Surname        Test Date        

Joe Bloggs         Joe                     Blogs           23/02/2022

Thanks for your assistance