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.