Forum Discussion
Generate Series from today
- 5 years ago
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)
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.⚡