Find Users Months sequence in power apps

[007James_Bond]

I have stuck one of the problem need your suggestion.

I have a requirement to find defaulters user name .In sharepoint have table named ABC that contain column Name,ID,Month,DataUsage(In MB)

We have a static value of Months in dropdown from 1 to 12 in numbers and one input box of Threshold limit

Sharepoint Table Look Like.

Name    ID    Month    DataUsage
Emp1    01     Jan-19   1000
Emp1    01     Dec-19    2000
Emp1    01     Nov-19   3000
Emp1    01     Sept-19   2500
Emp1    01     Oct-19    3000
Emp2    02     June-19   4000
Emp2    02      July-19   5000
Emp2    02      Aug-19   6000
Emp2    02      Sept-19  7000
Emp2    02      Nov-19   8000
Emp3    02      Jan-19    2000
Emp3   02       Feb-19   2500
Emp3   02       March-19 2800
Emp3   02       April-19 3000
Emp3   02       May-20 4000

Problem is to find whose users who have consumed threshold limit of data in continuously 'N' number of month.

Example :we provide threshold input box value 1000 MB and choose dropdown 05
that mean find the users who have consumed data morethan 1000MB in continuously last five month
Ans should be: only Emp1.
Emp2 have discontunity of month June,July,Aug,Sep and Oct but it have last month value Nov-19
Emp3 also have discontunity of month in May-2020

What i have done take a threshold filtered data in a collection seperately

ClearCollect(ColectionAA,Filter('Top Nutzer Daten', 'Nutzung in MB' >= TextInput1.Text*1))

I have filtered data basis of threshold limit and store in CollectionAA.

Now how can I further filter record to check month sequence.

I hope i have clarified the problem.
Please help me out .
Thanks in advance

[v-siky-msft]

Hi @007James_Bond ,

This is a quite complex requirement. Hope my code can help you.

The Output collection store the all employ names that meet the criteria.

ClearCollect(
    AD,
    AddColumns(
        RenameColumns(
            Filter(
                'SP list',
                DataUsage > Value(TextInputName.Text)
            ),
            "Name",
            "AName"
        ),
        "A",
        Round(
            DateDiff(
                Month,
                First(
                    Sort(
                        Filter(
                            'SP list',
                            Name = AName
                        ),
                        Month,
                        Descending
                    )
                ).Month
            ) / 30,
            0
        )
    )
); // Simplify the month to a number based on the largest month for each employee,
Clear(Result);
Clear(Output);
ForAll(
    AD As Allrecords,
    ForAll(
        Sequence(DropdownName.Selected.Value - 1) As No,
        If(
            (Allrecords.A + No.Value) in Filter(
                AD,
                AName = Allrecords.AName
            ).A,  // Determine if the user's months are continuous
            Collect(
                Result,
                {
                    Name: Allrecords.AName,
                    Value: true
                }
            ),
            Collect(
                Result,
                {
                    Name: Allrecords.AName,
                    Value: false
                }
            )
        )
    );
    If(
        Not(false in Result.Value),
        Collect(
            Output,
            {Value: Allrecords.AName}
        )
    );// Stores the employ names of all combinations that meet the criteria
    RemoveIf(
        Result,
        true
    )
)

Here is a demo.

Hope this helps.

Sik

[007James_Bond · Accepted solution]

thanks @v-siky-msft .

I like your way to design code in small chunks.