Image: vladim_ka/ Adobe Stock The short article How to understand if the Vehicle Date table is adequate when utilizing Power BI supplies a look into the inner functions of how Microsoft Power BI handles dates and times. Power BI develops an internal date table for you, but it will not constantly produce the filtering and grouping requirements that you need. When this takes place, you can think about creating the date table yourself. Having the skill to create your own is an advantage when you’re working with an intricate dataset.
In this tutorial, I’ll reveal you how to develop a date table when the internal default table isn’t sufficient using Data Analysis Expressions. If you’re not familiar with date tables, I recommend that you check out the connected post above prior to you take on developing a date table yourself.
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
I’m using Microsoft Power BI Desktop on a Windows 10 64-bit system. Throughout the short article, I will use the terms date table and realities table to explain the date table and data tables, respectively.
You can download the Microsoft Power BI demonstration filefor this tutorial.
What is a date table in Power BI?
A date table is a table of dates and other metadata about those dates. The relationship in between facts tables and the date table permits end consumers to filter and compare information by period, such as months and years.
It’s finest to know up front if you plan to develop a date table, due to the fact that Power BI enables only one date table. If you develop visuals on the internal Vehicle Date table and after that create your own date table and mark it as such, Power BI will ruin the Automobile Date table and all visuals based upon it.
A date table looks like any other table with a row for each date. The very first column is a date/time information type column called Date. The remaining columns shop metadata about each date such as the year, quarter, month and so on for the date, as you can see in Figure A.
Figure A
This date table satisfies all the requirements. When you use a custom date table, you manage the date hierarchies utilized by your design. This drips down to the fast procedures and other evaluations. It’s hard for a facts table to fulfill the requirements for being a date table:
- The date table should have a column called Date that’s a date/time data type.
- The Date column need to include special values.
- The Date column can’t include blank or null worths.
- The Date column can’t have missing out on dates– the dates need to be contiguous.
If you do select to use a facts table, you can define it as the date table as follows:
- Select the table in the Fields pane.
- Right-click the table and select Mark As Date Table and then choose Mark As Date table in the resulting menu.
This procedure can be frustrating, but if you mark the realities table as a date table, Power BI will construct the relationships and hierarchies based upon this table. If you do not, you’ll have to produce the essential relationships in between the date (realities) table and the other tables to get the same outcomes.
How to utilize DAX to develop a date table in Power BI
When you need to produce a custom-made date table, you can utilize DAX to develop a calculated table. DAX is an expression language used in Analysis Services, Power BI, and Power Pivot that includes functions and operators.
You can use either the DAX CALENDAR or CALEDARAUTO function to develop a date table. Both return a single-column table of dates.
When thinking about which operate to use, CALENDAR requires the first and last dates so it can generate a full list of dates. CALENDARAUTO utilizes existing dates in a realities table to produce a list of dates. We’ll use CALENDAR to produce a date table.
The CALENDAR function uses the following syntax:
CALENDAR(
where start_date is the first date in the resulting date table and end_date is the last. The function will return a one-column table occupied with a list of dates from start_date to end_date and every day in-between.
Now, let’s use CALENDAR to produce a date table in Power BI utilizing January 1, 2000, as start_date and December 31, 2021, as
- Launch Power BI. If needed, select New from the File menu so you’re working with a new.pbix file. You don’t want to operate in an existing.pbix file.
- Click Information in the left pane.
- To the far right, click the Write a DAX Expression to Create a New Table choice on the menu.
- Complete the expression, Table = CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)) (Figure B).
Figure B
Create a table with a list of dates. The function produces the new table, names the single column Date and occupies that column appropriately. It’s worth noting that there are 1,096 unique values, or rows. Nevertheless, 365 * 3 is 1,095. Power BI understands that 2020 was a leap year.
The next action is to add columns for each date component that you’ll need: week number, month number, quarter, year and so on. At this moment, you can use the Include Column alternative to include more columns. However, it’s more efficient to include them when you develop the table:
Date =
ADDCOLUMNS (
CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)),
“Year”, YEAR( [Date],
“MonthNumber”, FORMAT( [Date], “MM”),
“Quarter”, FORMAT ([ Date], “Q” ),
“DayOfWeek”, FORMAT ([ Date], “dddd” )
)
Figure C reveals the results. The variety of columns you add depends on the filtering and grouping needs of your visualizations and reports.
Figure C
You can use DAX to produce a date table. For this example, the function produces just a few columns, but there are many more you may need. For a total list, see Date and time functions (DAX)– DAX|Microsoft Learn.
This expression combines ADDCOLUMNS and CALENDAR:
- Date is the name of the resulting table.
- ADDCOLUMNS lets you define columns for Date.
- CALENDAR creates a date table and occupies the first column.
- The next four lines define the metadata columns.
At this point, things get a little muddy. You need to choose whether to mark the customized table as a date table as gone over previously in reference to marking a realities table as a date table. Doing so will develop the custom-made hierarchies specified by the date table.
On the other hand, if you ‘d like Power BI to do this for you, don’t mark the table as a date table. You can create the relationships and utilize it for specialized grouping and filtering rather. This is one of those locations where there’s no right or incorrect, however knowing your information is essential to making the most efficient option.
Keep in mind: When you mark a realities table as a date table, Power BI removes the built-in Auto Date table and any visuals you previously developed on it. If you deselect the date table, Power BI will instantly develop a new Automobile Date table.
Utilizing DAX to create a custom date table is easy if you know how to do so. There are other ways, and I’ll cover those in future posts.
