Forum Discussion

Jorgast's avatar
Jorgast
Resolver II
7 years ago
Solved

Date table including Interval

Hello Power BI Friends,

 

I need to create a date / interval table dataset. Currently i am using the Generate / Calendar function so i can get a rolling years. but i am not sure how to get the interval data. My data tables come in as 30 minute intervals by date. How to do i create a table that has the Date + time

 

Interval TEST =
GENERATE ( CALENDAR( DATE( YEAR( TODAY() ) - 3, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
 
Example:
 
DateIntervalDate Interval
6/1/201912:00 AM6/1/2019 0:00
6/1/201912:30 AM6/1/2019 0:30
6/1/20191:00 AM6/1/2019 1:00
6/1/20191:30 AM6/1/2019 1:30
6/1/20192:00 AM6/1/2019 2:00
6/1/20192:30 AM6/1/2019 2:30
6/1/20193:00 AM6/1/2019 3:00
6/1/20193:30 AM6/1/2019 3:30
6/1/20194:00 AM6/1/2019 4:00
6/1/20194:30 AM6/1/2019 4:30
6/1/20195:00 AM6/1/2019 5:00
6/1/20195:30 AM6/1/2019 5:30
6/1/20196:00 AM6/1/2019 6:00
6/1/20196:30 AM6/1/2019 6:30
6/1/20197:00 AM6/1/2019 7:00
6/1/20197:30 AM6/1/2019 7:30
6/1/20198:00 AM6/1/2019 8:00
6/1/20198:30 AM6/1/2019 8:30
6/1/20199:00 AM6/1/2019 9:00
6/1/20199:30 AM6/1/2019 9:30
6/1/201910:00 AM6/1/2019 10:00
6/1/201910:30 AM6/1/2019 10:30
6/1/201911:00 AM6/1/2019 11:00
6/1/201911:30 AM6/1/2019 11:30
6/1/201912:00 PM6/1/2019 12:00
6/1/201912:30 PM6/1/2019 12:30
6/1/20191:00 PM6/1/2019 13:00
6/1/20191:30 PM6/1/2019 13:30
6/1/20192:00 PM6/1/2019 14:00
6/1/20192:30 PM6/1/2019 14:30
6/1/20193:00 PM6/1/2019 15:00
6/1/20193:30 PM6/1/2019 15:30
6/1/20194:00 PM6/1/2019 16:00
6/1/20194:30 PM6/1/2019 16:30
6/1/20195:00 PM6/1/2019 17:00
6/1/20195:30 PM6/1/2019 17:30
6/1/20196:00 PM6/1/2019 18:00
6/1/20196:30 PM6/1/2019 18:30
6/1/20197:00 PM6/1/2019 19:00
6/1/20197:30 PM6/1/2019 19:30
6/1/20198:00 PM6/1/2019 20:00
6/1/20198:30 PM6/1/2019 20:30
6/1/20199:00 PM6/1/2019 21:00
6/1/20199:30 PM6/1/2019 21:30
6/1/201910:00 PM6/1/2019 22:00
6/1/201910:30 PM6/1/2019 22:30
6/1/201911:00 PM6/1/2019 23:00
6/1/201911:30 PM6/1/2019 23:30
6/2/201912:00 AM6/3/2019 0:00
6/2/201912:30 AM6/3/2019 0:30
6/2/20191:00 AM6/3/2019 1:00
6/2/20191:30 AM6/3/2019 1:30
6/2/20192:00 AM6/3/2019 2:00
6/2/20192:30 AM6/3/2019 2:30
6/2/20193:00 AM6/3/2019 3:00
6/2/20193:30 AM6/3/2019 3:30
6/2/20194:00 AM6/3/2019 4:00
6/2/20194:30 AM6/3/2019 4:30
6/2/20195:00 AM6/3/2019 5:00
6/2/20195:30 AM6/3/2019 5:30
6/2/20196:00 AM6/3/2019 6:00
6/2/20196:30 AM6/3/2019 6:30
6/2/20197:00 AM6/3/2019 7:00
6/2/20197:30 AM6/3/2019 7:30
6/2/20198:00 AM6/3/2019 8:00
6/2/20198:30 AM6/3/2019 8:30
6/2/20199:00 AM6/3/2019 9:00
6/2/20199:30 AM6/3/2019 9:30
6/2/201910:00 AM6/3/2019 10:00
6/2/201910:30 AM6/3/2019 10:30
6/2/201911:00 AM6/3/2019 11:00
6/2/201911:30 AM6/3/2019 11:30
6/2/201912:00 PM6/3/2019 12:00
6/2/201912:30 PM6/3/2019 12:30
6/2/20191:00 PM6/3/2019 13:00
6/2/20191:30 PM6/3/2019 13:30
6/2/20192:00 PM6/3/2019 14:00
6/2/20192:30 PM6/3/2019 14:30
6/2/20193:00 PM6/3/2019 15:00
6/2/20193:30 PM6/3/2019 15:30
6/2/20194:00 PM6/3/2019 16:00
6/2/20194:30 PM6/3/2019 16:30
6/2/20195:00 PM6/3/2019 17:00
6/2/20195:30 PM6/3/2019 17:30
6/2/20196:00 PM6/3/2019 18:00
6/2/20196:30 PM6/3/2019 18:30
6/2/20197:00 PM6/3/2019 19:00
6/2/20197:30 PM6/3/2019 19:30
6/2/20198:00 PM6/3/2019 20:00
6/2/20198:30 PM6/3/2019 20:30
6/2/20199:00 PM6/3/2019 21:00
6/2/20199:30 PM6/3/2019 21:30
6/2/201910:00 PM6/3/2019 22:00
6/2/201910:30 PM6/3/2019 22:30
6/2/201911:00 PM6/3/2019 23:00
6/2/201911:30 PM6/3/2019 23:30
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi Jorgast ,

     

    You can follow steps below to meet your requirement.

     

    1.Enter a time table from 00:00 – 23:30 , rename column Time.

     

    2.Use the formula below to create a calculate table.

    DateTime =
    SELECTCOLUMNS(
       CROSSJOIN (
           CALENDAR ( DATE ( 2019, 6, 1 ), TODAY()),
           'time'
       ),
       "DateTime", [Date] + [Time]
    )

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Jorgast ,

     

    You can follow steps below to meet your requirement.

     

    1.Enter a time table from 00:00 – 23:30 , rename column Time.

     

    2.Use the formula below to create a calculate table.

    DateTime =
    SELECTCOLUMNS(
       CROSSJOIN (
           CALENDAR ( DATE ( 2019, 6, 1 ), TODAY()),
           'time'
       ),
       "DateTime", [Date] + [Time]
    )

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Jorgast  ,

     

    Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

     

    Best regards

    Amy