Hi,
I am creating a teacher's timetable using a Sharepoint calendar and require to do a few calculations so trying to do it through PowerApps. I have a lot of fields in the table, but the key ones for this purpose are:
Teacher | Class | Category | Start Time | End Time | Duration
Duration is a sharepoint calculated column: =TEXT([End Time]-[Start Time],"hh:mm")
I have a GroupBy Gallery by Teacher -
GroupBy(TableTest,"Teacher","grpTeacher")
The problem I am having is to calculate the Total Duration overall and by combining categories.
You can see from the attached, the Total Hours should be 4.5 or 4h30m, not 4, same for Total Adults, it should not be 1.
Total Hours
Sum(Gallery9.AllItems, DateDiff('Start Time','End Time',Hours))
Total Adults
Sum(Filter(Gallery9.AllItems,Category.Value="Adults"), DateDiff('Start Time','End Time',Hours))
Help needed with:
I would be extremely grateful if someone can help me with this as I am still new and trying to find my way.
Many thanks,
Mo
Solved! Go to Solution.
You're in the right track with summing the values of the DateDiff function (which are numbers that don't have the issues with times mentioned by @Drrickryp ). However, DateDiff always returns a whole number of units, so this expression will return 1, not 1.5:
DateDiff(
DateTimeValue("2020-07-06T08:00:00Z"),
DateTimeValue("2020-07-06T09:30:00Z"),
Hours)
If you want to have fractional number of hours displayed, you can use the DateDiff function and find the number of minutes, then divide the result by 60:
Sum(
Gallery9.AllItems,
DateDiff('Start Time','End Time',Minutes) / 60)
And
Sum(
Filter(Gallery9.AllItems, Category.Value = "Adults"),
DateDiff('Start Time', 'End Time', Minutes) / 60)
Hope this helps!
Summing time is not a simple matter in PowerApps. I refer you to my go to resident expert on matters date and time @CarlosFigueira ref. https://powerusers.microsoft.com/t5/Building-Power-Apps/Sum-function-for-time-calculation/td-p/23476...
You're in the right track with summing the values of the DateDiff function (which are numbers that don't have the issues with times mentioned by @Drrickryp ). However, DateDiff always returns a whole number of units, so this expression will return 1, not 1.5:
DateDiff(
DateTimeValue("2020-07-06T08:00:00Z"),
DateTimeValue("2020-07-06T09:30:00Z"),
Hours)
If you want to have fractional number of hours displayed, you can use the DateDiff function and find the number of minutes, then divide the result by 60:
Sum(
Gallery9.AllItems,
DateDiff('Start Time','End Time',Minutes) / 60)
And
Sum(
Filter(Gallery9.AllItems, Category.Value = "Adults"),
DateDiff('Start Time', 'End Time', Minutes) / 60)
Hope this helps!
OMG, you guys are amazing and quick @CarlosFigueira @Drrickryp Thank you so so much. It has worked.
Any idea how I can Sum for multiple categories? i.e. combined Primary, Secondary & Early Years
Thank you once again.
You can have multiple categories in your filter expression:
Sum(
Filter(
Gallery9.AllItems,
Category.Value = "Primary" Or Category.Value = "Secondary" Or Category.Value = "Early Years"),
DateDiff('Start Time', 'End Time', Minutes) / 60)
Or if you want all categories that are not Adult, you can also use something like
Sum(
Filter(
Gallery9.AllItems,
Category.Value <> "Adult"),
DateDiff('Start Time', 'End Time', Minutes) / 60)
Hope this helps!
User | Count |
---|---|
184 | |
123 | |
91 | |
47 | |
42 |
User | Count |
---|---|
271 | |
159 | |
132 | |
85 | |
78 |