Forum Discussion
Generate Series from today
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
)
)
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)
2 Replies
- parry2k
Super User
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.⚡
PaulDBrownCommunity Champion
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)
