cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwi1
Post Patron
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

 

datetime_before_07:15time_after_07:15
29-6-202129-6-2021 07:0029-6-2021 07:41
30-6-202130-6-2021 06:2830-6-2021 07:35
1 ACCEPTED SOLUTION
Mohammad_Refaei
Solution Specialist
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

 

View solution in original post

4 REPLIES 4
Mohammad_Refaei
Solution Specialist
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

 

View solution in original post

Hi @Mohammad_Refaei 

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

Mohammad_Refaei
Solution Specialist
Solution Specialist

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

@Mohammad_Refaei 

Super, also solved!

John

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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 Kudoed Authors