top of page
  • Writer's pictureVahid

How to create a Date/Time Table with 1-hour increments on each row using DAX

Updated: Aug 17, 2023



You can create a date table with different DAX functions, such as CALENDAR and CALENDARAUTO, which return a table containing a single column named "Date", but these two functions cannot create a date/time series table. This article describes step by step how to create a table with 24 rows for each day to cover 24 hours in a data model.


GENERATESERIES


To create a Date/Time table instead of using Calendar functions, the GENERATESERIES needs to be used. The GENERATESERIES will return a single column table containing the values of an arithmetic series, that is, a sequence of values in which each differs from the preceding by a constant quantity. The name of the column returned is Value.


Syntax : GENERATESERIES(<startValue>, <endValue>[, <incrementValue>])


How to set the parameters to create a Date/Time table that increases 1 hour from a date until Now


The GENERATESERIES has three paraments as follows:

Term

Definition

startValue

The initial value used to generate the sequence.

endValue

The end value used to generate the sequence.

incrementValue

(Optional) The increment value of the sequence. When not provided, the default value is 1.

The formula for each parameter:

startValue: use any DAX expression that returns a DateTime value. The output will start from this date and time. For this article, I used DATE( 2021, 01, 01 ) as a Start Value.

Note: When you use the Date function, it will return the selected date with 12:00:00 AM time, so if you want to change the start time, add time to the Date formula like this:

DATE( 2021, 01, 01 )+TIME(17,00,00)

the above formula will return 1/01/2021 5:00:00 PM.

endValue: Any DAX expression that returns a DateTime value. The output will be the end of the series. In this article, I want to create a dynamic series of values that will start from startValue (01-Jan-2021) until the current date and time, so I used the NOW() function.

NOW function returns the current date and time in DateTime format.

Note:

  • The result of the NOW function changes only when the column that contains the formula is refreshed. It is not updated continuously.

  • In the Power BI Service, the result of the NOW function is always in the UTC timezone.

  • The TODAY function returns the same date but is not precise with regard to time; the time returned is always 12:00:00 AM, and only the date is updated.

incrementValue: This parameter will define the increment applied when generating the series; in this article, this parameter needs to be 1 hour, so I used the 0.041666667 that is equal to 1h.


Now all GENERATESERIES parameters are defined as follow, and we can use that to create a Date/Time Table:


Table = GENERATESERIES (DATE (2021, 01, 01), NOW (), 0.041666667))

The output:




Now you have a table with one column that has a series of Dates/Times that increase 1 hour from a selected date until Now.

If you want to add more columns to the table to make it like a Calendar table, try this code:


Table = VAR _DT = GENERATESERIES (DATE(2021,01,01), NOW(), 0.041666667) RETURN ADDCOLUMNS ( _DT, "Year", YEAR ( [Value] ), "Month", MONTH ( [Value] ), "Day", DAY ( [Value] ), "Day Name", FORMAT ( [Value], "DDDD"), "Day of Week", WEEKDAY ( [Value] ), "Week of Year", WEEKNUM ( [Value] ), "Start of Month", DATE (YEAR ( [Value] ), MONTH ( [Value] ), 1), "End of Month", EOMONTH ( [Value], 0), "YearMonth", INT (FORMAT ( [Value], "YYYYMM")), "MonthYear", FORMAT ( [Value], "MMM-YY"), "Quarter", QUARTER ( [Value] ), "Time",TIMEVALUE([Value]) )

Output:




If you want to create a Calendar table in Power Query, please check my previous post about Creating a Calendar Table in Power Query with 3 steps.


5 comments

5 Comments


Guest
Mar 27, 2023

Using this script the time values change from being on the hour to being x:59:59 on Nov. 28, 2021

Like
Vahid
Vahid
Aug 16, 2023
Replying to

I changed the code to fix the issue in the milisecond section, sorry for the late response

Let me know if there are any issues with the new one.

Like

Guest
Jul 27, 2022

Useful blog post, Thanks for sharing

Like
Vahid
Vahid
Nov 16, 2022
Replying to

Thanks.

Like
bottom of page