Creating a Date/Time Table that increases 1 hour on each row using DAX
There are several DAX functions to create a Date Table like CALENDAR and CALENDARAUTO, which return a table with a single column named "Date" containing a contiguous set of dates, but these two functions can not be used to create a Date/Time table with time increases.
You may have worked on a data model and needed a Date/Time table with 24 rows for each day to cover 24 hours rather than Calendar Table with 1 row for each day. This article will explain how to create that table step by step.
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 follow:
The initial value used to generate the sequence.
The end value used to generate the sequence.
(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.
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 TIME( 01, 00, 00 ) function and set that to return 1h. Time function has 3 parameters TIME(hour, minute, second) and returns a time (DateTime) ranging from 00:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 PM).
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 (), TIME (01, 00, 00))
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:
GENERATESERIES (DATE (2021, 01, 01), NOW (), TIME (01, 00, 00))
"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] )
Download a sample file:
If you want to create a Calendar table in Power Query, see my previous post about Creating a Calendar Table in Power Query with 3 steps.