topic Re: GENERATESERIES of Dates and round Hours in DAX Commands and Tips

GENERATESERIES of Dates and round Hours

BLB — Mon, 26 Sep 2022 14:04:30 GMT

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

Re: GENERATESERIES of Dates and round Hours

Greg_Deckler — Mon, 26 Sep 2022 14:37:33 GMT

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

Re: GENERATESERIES of Dates and round Hours

serpiva64 — Mon, 26 Sep 2022 15:23:16 GMT

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 !

Re: GENERATESERIES of Dates and round Hours

BLB — Mon, 26 Sep 2022 16:05:04 GMT

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.

Re: GENERATESERIES of Dates and round Hours

Greg_Deckler — Mon, 26 Sep 2022 16:10:47 GMT

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

Re: GENERATESERIES of Dates and round Hours

v-yueyunzh-msft — Tue, 27 Sep 2022 02:47:18 GMT

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

Re: GENERATESERIES of Dates and round Hours

BLB — Tue, 27 Sep 2022 10:46:49 GMT

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

Re: GENERATESERIES of Dates and round Hours

BLB — Tue, 27 Sep 2022 11:07:53 GMT

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

Re: GENERATESERIES of Dates and round Hours

BLB — Tue, 27 Sep 2022 11:16:32 GMT

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#M107662