cancel
Showing results for
Did you mean:
Post Patron

## time calculation

Dear all,

I have a question about the following.

Below is a small table with date-times.

I want to show the date_time which is the closest to 07:15.

Both the closest before 07:15 and the closest after 07:15.

I have a calendar table, so I want to do this for every date in the table.

In the second table, the outcome is shown.

 time_executed 29-6-2021 07:00 29-6-2021 07:41 29-6-2021 12:31 29-6-2021 13:38 29-6-2021 13:54 29-6-2021 14:17 29-6-2021 15:53 29-6-2021 19:06 29-6-2021 19:24 29-6-2021 20:28 29-6-2021 22:20 29-6-2021 22:21 30-6-2021 04:42 30-6-2021 04:49 30-6-2021 04:50 30-6-2021 04:59 30-6-2021 05:00 30-6-2021 05:02 30-6-2021 05:20 30-6-2021 05:30 30-6-2021 06:09 30-6-2021 06:18 30-6-2021 06:18 30-6-2021 06:28 30-6-2021 07:35

 date time_before_07:15 time_after_07:15 29-6-2021 29-6-2021 07:00 29-6-2021 07:41 30-6-2021 30-6-2021 06:28 30-6-2021 07:35
1 ACCEPTED SOLUTION
Solution Specialist

Try this:

``````Before 7:15 =
CALCULATE (
MAX ( [time_executed] ),
FILTER (
YourTable[time_executed],
YourTable[time_executed] < 'Calendar'[Date] + 0.302083333333333
)
)``````

``````After 7:15 =
CALCULATE (
MIN ( [time_executed] ),
FILTER (
YourTable[time_executed],
YourTable[time_executed] > 'Calendar'[Date] + 0.302083333333333
)
)``````

P.S.  0.302083333333333 is the numerical value of 7:15 AM

4 REPLIES 4
Solution Specialist

Try this:

``````Before 7:15 =
CALCULATE (
MAX ( [time_executed] ),
FILTER (
YourTable[time_executed],
YourTable[time_executed] < 'Calendar'[Date] + 0.302083333333333
)
)``````

``````After 7:15 =
CALCULATE (
MIN ( [time_executed] ),
FILTER (
YourTable[time_executed],
YourTable[time_executed] > 'Calendar'[Date] + 0.302083333333333
)
)``````

P.S.  0.302083333333333 is the numerical value of 7:15 AM

Post Patron

Is there a way to calculate the numeric value of a certain time?

Solution Specialist

Yes... The easiest way is to enter it in Excel cell then change the cell format to decimal 🙂

Post Patron

Super, also solved!

John

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors