Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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
)
)
1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community 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)

result.JPG





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
parry2k
Super User
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.

 



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.

PaulDBrown
Community Champion
Community 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)

result.JPG





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.