Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Is there a way to calculate the numeric value of a certain time?
Yes... The easiest way is to enter it in Excel cell then change the cell format to decimal 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |