time calculation

[Anonymous]

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

[Mohammad_Refaei · Accepted solution]

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

[Anonymous]

Hi @Mohammad_Refaei 

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

[Mohammad_Refaei]

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

[Anonymous]

@Mohammad_Refaei 

Super, also solved!

John