Generate Series from today

[Anonymous]

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
 )
 )

[parry2k]

@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.⚡

[PaulDBrown · Accepted 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)