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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BLB
Helper I
Helper I

GENERATESERIES of Dates and round Hours

Hi, 

 

I am trying to create a data series with every round hour between two dates. I came up with the below DAX code, but after a certain date it starts returning ..... ..:59:59 instead of ..... ..:00:00 .

 

Can somebody advise what is causing it and how to resolve it?

 

 

DateHour Master =

VAR YearMinusOne = YEAR(NOW()) -1
VAR YearPlusOne =  YEAR(NOW()) +1

RETURN
GENERATESERIES (
        DATE ( YearMinusOne, 1, 1 ),
        DATE ( YearPlusOne, 12, 31 ),
        1/24
    )
  
BLB_0-1664200903992.png

 

Thanks

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @BLB 

According to your description, you wabt to create a data series with every round hour between two dates.Right?

Here are the steps you can follow:

(1)This is my test data to situmulate your fact table:

vyueyunzhmsft_0-1664246684467.png

(2)We can click "New table" and enter this dax:

 

DateHour Master = 
    VAR __YearMinusOne = YEAR(TODAY()) - 1
    VAR __YearPlusOne = YEAR(TODAY())+1 
    VAR __Table =
            CALENDAR(DATE(__YearMinusOne,1,1),DATE(__YearPlusOne,12,31))
    var _time= SELECTCOLUMNS( GENERATESERIES(
    Time(0,0,0),
    TIME(23,0,0),
        1/24) , "time", [Value] )
    var _t = CROSSJOIN(__Table,_time)
RETURN
   SELECTCOLUMNS( ADDCOLUMNS(_t,"tt",[Date]+[time]) ,"DateTime" , [tt])

 

(3)Then we can get the table and create relationship between two tables, the result is as follows:

vyueyunzhmsft_1-1664246765801.pngvyueyunzhmsft_2-1664246781538.png

 

Best Regards,

Aniya Zhang

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

 

 

View solution in original post

8 REPLIES 8
v-yueyunzh-msft
Community Support
Community Support

Hi, @BLB 

According to your description, you wabt to create a data series with every round hour between two dates.Right?

Here are the steps you can follow:

(1)This is my test data to situmulate your fact table:

vyueyunzhmsft_0-1664246684467.png

(2)We can click "New table" and enter this dax:

 

DateHour Master = 
    VAR __YearMinusOne = YEAR(TODAY()) - 1
    VAR __YearPlusOne = YEAR(TODAY())+1 
    VAR __Table =
            CALENDAR(DATE(__YearMinusOne,1,1),DATE(__YearPlusOne,12,31))
    var _time= SELECTCOLUMNS( GENERATESERIES(
    Time(0,0,0),
    TIME(23,0,0),
        1/24) , "time", [Value] )
    var _t = CROSSJOIN(__Table,_time)
RETURN
   SELECTCOLUMNS( ADDCOLUMNS(_t,"tt",[Date]+[time]) ,"DateTime" , [tt])

 

(3)Then we can get the table and create relationship between two tables, the result is as follows:

vyueyunzhmsft_1-1664246765801.pngvyueyunzhmsft_2-1664246781538.png

 

Best Regards,

Aniya Zhang

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

 

 

Hi Aniya, 

Thank you it does work!

In the meantime I came across this article with a similar solution, but I find yours more elegant 🙂

 

https://community.powerbi.com/t5/Desktop/how-to-build-a-calendar-table-with-date-and-time/m-p/242658...

serpiva64
Super User
Super User

Hi, 

you can also add a column

DateTime = format('DateHour Master'[Value],"dd/mm/yyyy hh:00:00")
and it function
serpiva64_0-1664205782905.png

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

 

 

Hi @serpiva64 it does work, thank you!

But I don't really understand how the formatting will result in rounding - can you please explain, e.g. why will 11:59:59 turn into 12:00:00? 

Thanks

Greg_Deckler
Super User
Super User

@BLB Try this and then set your Value column to type Date/time.

DateHour Master = 
    VAR __YearMinusOne = YEAR(NOW()) - 1
    VAR __YearPlusOne = YEAR(NOW()) + 1
    VAR __Table =
        ADDCOLUMNS(
            GENERATESERIES(
                DATE(__YearMinusOne,1,1),
                DATE(__YearPlusOne, 12, 31),
                1/24
            ),
            "Date Time",ROUNDUP([Value],5)
        )
RETURN
    SELECTCOLUMNS(__Table,"Value",[Date Time])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler , thank you for your prompt reply.

I tested it and it still behaves funny. In the generated table I can now see all the rounded dates&hours indeed. However, when I connect it with my fact table, it will not recognise some of the generated values, like the ones highlighted below: left column is from the fact table, right is from the generated table.

Do you have any suggestion why?

BLB_0-1664208092955.png

 Thanks.

@BLB How about this?

DateHour Master = 
    VAR __YearMinusOne = YEAR(NOW()) - 1
    VAR __YearPlusOne = YEAR(NOW()) + 1
    VAR __Table =
        ADDCOLUMNS(
            GENERATESERIES(
                DATE(__YearMinusOne,1,1),
                DATE(__YearPlusOne, 12, 31),
                1/24
            ),
            "Date Time",DATE(YEAR([Value]),MONTH([Value]),DAY([Value])) + TIME(HOUR([Value]),0,0)
        )
RETURN
    SELECTCOLUMNS(__Table,"Value",[Date Time])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks but still something weird keeps happinging with this. Somehow it generates the value 20/01/2021 21:00:00 twice!

BLB_0-1664275576194.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors