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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors