cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Frequent Visitor

## Calculate date-hour-minute to generate rating

Hi all,

Can you please give me an advice on how to do it?

I have created two columns from my SP list, namely, DEADLINE and DATE COMPLETED. By using powerapps, I want to generate a score based from the date and time spent by the user. For example, if the user selected Feb 20 2019, 11:59pm for the DEADLINE and the user selected Feb 21 2019, 00:00am for the DATE COMPLETED, the user will receive a 75% rating.

I was only able to generate a score of a 100% if the user successfully finished the task on or before the DEADLINE and 65% if the selected date was overdue.

Thank you in advance for your help.

3 REPLIES 3
Highlighted
Solution Sage

## Re: Calculate date-hour-minute to generate rating

There are a couple of ways to go about it, with varying speeds of process. You might need to test to find the best way. Also, it helps to understand the various options that might be available in case they might help you solve another problem down the line.

The important part to all of these solutions is that the DateDiff() function takes a Units argument that determines the unit of the answer. If you don't provide it, DateDiff() returns a value of days, which is probably why you aren't seeing the nuance of each potential score deviation.

Solve it with an If() Statement

The If() statement is already branch-able, so you don't need to embed another If()... but to do it with an if statement will require a test for each level of result in your table.

```Set( varTimeliness,
If(
DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes) <= 0, 100,
DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes) <= 179, 95,
DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes) <= 299, 90,
DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes) <= 419, 85,
DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes) <= 539, 80,
DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes) <= 659, 75,
DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes) <= 779, 70,
65
)```

Solve it with a Collection

This would be a more forward-scalable solution, so a situation where your scoring matrix might change in the future would argue for a setup like this... though it might be quicker in process (and easier to maintain) even without the idea that your requirements might change in the future.

The basic idea would be to establish a Collection where you have a matrix of 2 fields per entry: the score, and the max minutes to earn that score. That can be set up early in your app, even in the OnStart() property. Then you would check the results of the DateDiff() function against the Collection, using a statement like:

`First(    SortByColumns(        Filter(            yourCollection,            yourMaxMinutesField >= DateDiff(DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes)        ),        SortOrder.Ascending    )).yourScoreField`

So, starting from the inside out...

Filter()... pulls all of the records where the yourMaxMinutes field is greater than the DateDiff() function... meaning, the best score the person qualifies for and all the rest.

SortByColumns()... puts those in ascending order... meaning that the smallest yourMaxMinutes will be the first record in the returned set, which represents the best score they could qualify for.

First().yourScoreField... gets the first record (the best score), and specifically returns the score field from that record.

Either one of these would work. The second one consolidates any future alterations to the grading or formula to one location (the point where you create  your Collection) rather than anywhere you want to generate a score. You can reference that Collection everywhere, and your formula to return the score will always remain the same. If you have 20 places where you compare numbers to generate the score,  you only have to change the solution plot once - at the Collection - rather than for each of the 20 implementations.

Post back if you need help with the ClearCollect() statement to create the Collection.

Highlighted
Frequent Visitor

## Re: Calculate date-hour-minute to generate rating

Hi @TimRohr,

Thanks for the info!!

I would like to prefer to solve it by using a collection. May I ask how to create the collection containing the score/rating and date-hr-mm to integrate it with the form.

Thank you!

Highlighted
Solution Sage

## Re: Calculate date-hour-minute to generate rating

Hi, @fjose ...

Your Collection won't deal with date values, since we're going to rely on the DateDiff() function to return a simple value in minutes. It will be a simple set of 2 numbers:

```ClearCollect( ScoreMatrix,
{MaxMinutes: 0, ScoreResult: 100},
{MaxMinutes: 179, ScoreResult: 95},
{MaxMinutes: 299, ScoreResult: 90},
{MaxMinutes: 419, ScoreResult: 85},
{MaxMinutes: 539, ScoreResult: 80},
{MaxMinutes: 659, ScoreResult: 75},
{MaxMinutes: 779, ScoreResult: 70}
)```

Put that in your OnStart() property for the app and you can access it from any screen.

Then, when  you need the ScoreResult, use the formula I previously posted (modified here with the field names and Collection name updated to match the above):

`First(    SortByColumns(        Filter(            ScoreMatrix,            MaxMinutes >= DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes)        ),        MaxMinutes,        SortOrder.Ascending    )).ScoreResult`

(I forgot the sort field in my initial treatment of this formula, so I wanted to point it out, here.)

(Also, I had doubled up my DateDiff() statement... a mistake.)

Note that the 65% case is not represented in the Collection. You have a couple of options as it relates to that. Since we are using a "Max" setup to the numbers, and a 65% score has no maximum value (according to your table, a user could finish 13 hours after the dealine or 13 years, and it would still be a 65%), it doesn't cleanly fit into our logic.

Option 1: You can hardcode an upper limit to the 65% in the Collection that is absurdly beyond any use case you might expect. Say, 50000 minutes (nearly 35 days). Of course, what happens when some case surpasses this...?

Option 2: Check the Max() of the MaxMinutes value in your Collection to drive whether you need to implement the score-derivation formula. So, modify your scoring formula to be:

`If( DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes) > Max(ScoreMatrix, MaxMinutes),    65,    First(        SortByColumns(            Filter(                ScoreMatrix,                MaxMinutes >= DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes)            ),            MaxMinutes,            SortOrder.Ascending        )    ).ScoreResult)`

That should cover the case that your user is well beyond the scope of what you'd expect in terms of project completion.

Note: the Max() formula could provide a way to handle the basic score formula instead of doing the First(SortByColumns(Filter(...),...)), and you might find it simpler. Sometimes we are creatures of habit, as I was when I put the formula together the first time. Still, you might explore whether the Max() treatment of the score-derivation formula might be quicker... something like...

```Max(
Filter(
ScoreMatrix,
MaxMinutes >= DateDiff( DatePicker1.SelectedDate, DatePicker2.SelectedDate, Minutes)
),
ScoreResult
)```

Worth looking at...

## Helpful resources

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,838)