cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
)

Thanks

1 ACCEPTED SOLUTION
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:

(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

(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

8 REPLIES 8
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:

(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

(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

Helper I

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...

Super User

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 !

Helper I

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

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 =
GENERATESERIES(
DATE(__YearMinusOne,1,1),
DATE(__YearPlusOne, 12, 31),
1/24
),
"Date Time",ROUNDUP([Value],5)
)
RETURN
SELECTCOLUMNS(__Table,"Value",[Date Time])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Super User

``````DateHour Master =
VAR __YearMinusOne = YEAR(NOW()) - 1
VAR __YearPlusOne = YEAR(NOW()) + 1
VAR __Table =
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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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