Answered

Find Users Months sequence in power apps

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Verified answer

007James_Bond 58 on at

Like
a
(0)

Report
Copy link

Link copied!

thanks @v-siky-msft .

I like your way to design code in small chunks.

Was this reply helpful? Yes No