cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
007James_Bond
Helper I
Helper I

Find Users Months sequence in power apps

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

1 ACCEPTED SOLUTION

Accepted Solutions

thanks @v-siky-msft .

 

I like your way to design code in small chunks.

 

View solution in original post

2 REPLIES 2
v-siky-msft
Community Support
Community Support

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.

GIF_91.gif

Hope this helps.

Sik

thanks @v-siky-msft .

 

I like your way to design code in small chunks.

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,001)