cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fjose
Level: Powered On

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.

 

rating.PNG 

 

 

varTimeliness.PNG

 

3 REPLIES 3
TimRohr
Level 10

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.

 

 

fjose
Level: Powered On

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!

TimRohr
Level 10

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,876)