Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I'm trying to create a table with 15 minute appointments from today onwards ( or possibly between two dates if it's easier)
I'm using the following to create a table but I'm having trouble filtering it to start from today's date time i.e. now
Can this be done?
Thanks in advance
Appointment Times =
VAR Opening = 9
VAR Closing = 17
VAR TimeSlot = 30
VAR Offset = ( TimeSlot / 2 )
RETURN
GENERATE (
GENERATESERIES ( 0, 1439, 1 ),
// 24 hours/day * 60 minutes/hour = 1,440 - 1 (initial time 12am)
VAR __Minutes = [Value]
VAR __Time =
TIME ( 0, __Minutes, 0 )
// MROUND: Rounds Value to the nearest multiple of TimeSlot
VAR __MinutesRounded =
MROUND ( ( __Minutes + Offset ), TimeSlot )
VAR __StartTimeSlotRounded =
TIME ( 0, __MinutesRounded - TimeSlot, 0 )
VAR __EndTimeSlotRounded =
TIME ( 0, __MinutesRounded, 0 )
RETURN
ROW (
"Hour", HOUR ( __Time ),
"Minute", MINUTE ( __Time ),
"Hour Minute 24h", FORMAT ( __Time, "hh:mm" ),
"Hour Minute 12h", FORMAT ( __Time, "hh:mm AM/PM" ),
"30 Minute Slot Start", FORMAT ( __StartTimeSlotRounded, "hh:mm" ),
"30 Minute Slot End", FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
"30 Minute Slot", FORMAT ( __StartTimeSlotRounded, "hh:mm" ) & "-"
& FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
"Working Hours", IF (
( __Minutes / 60 ) >= Opening
&& ( __Minutes / 60 ) <= Closing,
"Yes",
"No"
),
"Time", __Time,
"TimeSlot Start", __StartTimeSlotRounded,
"TimeSlot End", __EndTimeSlotRounded
)
)
Solved! Go to Solution.
Can you post a depiction of the expected result? At the moment the code delivers a table with time values, right?
Edit: see if this works for you:
Appointment Times with date =
VAR Opening = 9
VAR Closing = 17
VAR TimeSlot = 30
VAR Offset = ( TimeSlot / 2 )
RETURN
GENERATE (
GENERATESERIES ( 0, 1439, 1 ),
// 24 hours/day * 60 minutes/hour = 1,440 - 1 (initial time 12am)
VAR __Minutes = [Value]
VAR __Time =
TIME ( 0, __Minutes, 0 ) // MROUND: Rounds Value to the nearest multiple of TimeSlot
VAR __MinutesRounded =
MROUND ( ( __Minutes + Offset ), TimeSlot )
VAR __StartTimeSlotRounded =
TIME ( 0, __MinutesRounded - TimeSlot, 0 )
VAR __EndTimeSlotRounded =
TIME ( 0, __MinutesRounded, 0 )
VAR TimeValues =
ROW (
"Hour", HOUR ( __Time ),
"Minute", MINUTE ( __Time ),
"Hour Minute 24h", FORMAT ( __Time, "hh:mm" ),
"Hour Minute 12h", FORMAT ( __Time, "hh:mm AM/PM" ),
"30 Minute Slot Start", FORMAT ( __StartTimeSlotRounded, "hh:mm" ),
"30 Minute Slot End", FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
"30 Minute Slot",
FORMAT ( __StartTimeSlotRounded, "hh:mm" ) & "-"
& FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
"Working Hours",
IF (
( __Minutes / 60 ) >= Opening
&& ( __Minutes / 60 ) <= Closing,
"Yes",
"No"
),
"Time", __Time,
"TimeSlot Start", __StartTimeSlotRounded,
"TimeSlot End", __EndTimeSlotRounded
)
VAR Day2 =
TODAY () + 2
VAR Calend =
CALENDAR ( TODAY (), Day2 )
RETURN
ADDCOLUMNS (
CROSSJOIN ( Calend, TimeValues ),
"DateTime",
FORMAT ( [Date] & " " & [Hour Minute 24h], "General Date" ),
"DateTime Start",
FORMAT ( [Date] & " " & [30 Minute Slot Start], "General Date" ),
"DateTime End",
FORMAT ( [Date] & " " & [30 Minute Slot End], "General Date" )
)
)
which gets you this (showing the last columns including the date)
Proud to be a Super User!
Paul on Linkedin.
@Anonymous if you just want a 15-minute interval, use the following DAX code. You can add an extra column as you see fit, this will have 15 minutes for each date from today, and you can change the value in __totalDays to add future dates.
Table =
VAR __startDate = TODAY()
VAR __totalDays = 0
VAR __endDate = TODAY() + __totalDays
VAR __calendar = CALENDAR ( __startDate, __endDate )
VAR __time = CROSSJOIN ( __calendar, GENERATESERIES ( 0, 1425, 15 ) )
VAR __addDateTime = ADDCOLUMNS ( __time, "DateTime", [Date] + TIME ( 0, [Value], 0 ) )
return __addDateTime
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Can you post a depiction of the expected result? At the moment the code delivers a table with time values, right?
Edit: see if this works for you:
Appointment Times with date =
VAR Opening = 9
VAR Closing = 17
VAR TimeSlot = 30
VAR Offset = ( TimeSlot / 2 )
RETURN
GENERATE (
GENERATESERIES ( 0, 1439, 1 ),
// 24 hours/day * 60 minutes/hour = 1,440 - 1 (initial time 12am)
VAR __Minutes = [Value]
VAR __Time =
TIME ( 0, __Minutes, 0 ) // MROUND: Rounds Value to the nearest multiple of TimeSlot
VAR __MinutesRounded =
MROUND ( ( __Minutes + Offset ), TimeSlot )
VAR __StartTimeSlotRounded =
TIME ( 0, __MinutesRounded - TimeSlot, 0 )
VAR __EndTimeSlotRounded =
TIME ( 0, __MinutesRounded, 0 )
VAR TimeValues =
ROW (
"Hour", HOUR ( __Time ),
"Minute", MINUTE ( __Time ),
"Hour Minute 24h", FORMAT ( __Time, "hh:mm" ),
"Hour Minute 12h", FORMAT ( __Time, "hh:mm AM/PM" ),
"30 Minute Slot Start", FORMAT ( __StartTimeSlotRounded, "hh:mm" ),
"30 Minute Slot End", FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
"30 Minute Slot",
FORMAT ( __StartTimeSlotRounded, "hh:mm" ) & "-"
& FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
"Working Hours",
IF (
( __Minutes / 60 ) >= Opening
&& ( __Minutes / 60 ) <= Closing,
"Yes",
"No"
),
"Time", __Time,
"TimeSlot Start", __StartTimeSlotRounded,
"TimeSlot End", __EndTimeSlotRounded
)
VAR Day2 =
TODAY () + 2
VAR Calend =
CALENDAR ( TODAY (), Day2 )
RETURN
ADDCOLUMNS (
CROSSJOIN ( Calend, TimeValues ),
"DateTime",
FORMAT ( [Date] & " " & [Hour Minute 24h], "General Date" ),
"DateTime Start",
FORMAT ( [Date] & " " & [30 Minute Slot Start], "General Date" ),
"DateTime End",
FORMAT ( [Date] & " " & [30 Minute Slot End], "General Date" )
)
)
which gets you this (showing the last columns including the date)
Proud to be a Super User!
Paul on Linkedin.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
82 | |
66 | |
49 |
User | Count |
---|---|
137 | |
111 | |
101 | |
66 | |
65 |