GENERATESERIES of Dates and round Hours

[BLB]

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

    )

Thanks

[v-yueyunzh-msft · Accepted solution]

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:

(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:

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

[BLB]

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]

Hi, 

you can also add a column

DateTime = format('DateHour Master'[Value],"dd/mm/yyyy hh:00:00")

and it function

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 !

[BLB]

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]

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

[BLB]

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?

 Thanks.

[Greg_Deckler]

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

[BLB]

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