Calculating an average of a filtered sharepoint list

(0) Share

Report

Posted on by JürgenD

166

Hello everyone,

i would like to get statistic values of a sharepont list.

We organize suggestions for improvement of our employees and i would like to know what is the average duration between submit date and enddate of a suggestion. This average shall only consider suggestions that were submitted in a defined period of time.

To clarify what i mean i show a table:

SuggestionID	SubmitDate	Enddate
1	01.05.2022	20.05.2022
2	01.06.2022	10.06.2022
3	01.01.2023	10.01.2023

For example evaluationperiod is 01.01.2022 to 31.12.2022

- Suggestion ID3 will not be considered.

- For ID1 the processing time is 20 days, for ID2 it is 10 days.

- So the average for 2022 is 15 days.

How do i put this into a formula?

Looking forward for your help!

Jürgen

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@JürgenD

Average(AddColumns(table, "worked", DateDiff(SubmitDate, EndDate), worked})

Was this reply helpful? Yes No
JürgenD 166 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your help,
unfortunately it does'n work:
PowerApps tells me:
"Invalid argumemt type table. Instead, a number value is expected"
Average(AddColumns(BVW_Vorschlag; "Duration";DateDiff(VorschlagsDatum;Abschlussdatum)))
What is the second "worked" in the formula for? And hwat about this closing bracket }?
Can you help again, please?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 155,171 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@JürgenD ,

Firstly the formula posted by @Drrickryp is correct if placed in a Label on the screen, but your translation to your values is missing the field to average - it should be

Average( AddColumns( BVW_Vorschlag; "Duration"; DateDiff( VorschlagsDatum; Abschlussdatum ) ), Duration )

If you want a specific period, add a couple of Date Pickers

Average( AddColumns( Filter( BVW_Vorschlag; SubmitDate >=SubmitDatePicker.SelectedDate && EndDate <= EndDatePicker.SelectedDate ); "Duration"; DateDiff( VorschlagsDatum; Abschlussdatum ) ), Duration )

Was this reply helpful? Yes No
JürgenD 166 on at

Like (0)

Report
Copy link

Link copied!

Hello Warren,
thank you again for your help. After many years of programming with VBA i still have problems to understand the powerapp syntax. Now i got it - at least in this case :-). Thanks to Derrick too.
I am happy bout people like you for their help!

Was this reply helpful? Yes No