Working with Dates in Power BI
How to create your own Power BI date table using DAX
In today’s example, we will be using the dashboard we have created from the previous newsletter so you can download it and follow along.
If we check the underlying data, we can see that it has only one table and that is financials table which contains all sales information for a specific period of time.
As we can see from the Model View, this data table contains Date column and one might say, why do we need to create a date table when we have all of the dates in financials table.
There are a lot of reasons on why we need a separate date table which will serve as a filter for our fact table (read more about Star schema to learn about fact table and dimension tables). Some of those reasons include but are not limited to:
Your fact table might be missing values for specific days and that will mess up analysis,
Fiscal year end for your company is different from year end (it can be June),
You want to use Time Intelligence functions do analysis such as YTD ( Year To Date), MTD (Month To Date), YoY (Year over Year), MoM (Month over Month) etc.,
You can customize your calendar as needed (such as adding holidays, identifying weekends).
There are different methods to create a calendar. We will be using DAX in order to create a calendar as that is a pretty straight forward process.
In order to do so, simply go to Table tools and click on New table, which will show you the formula bar where you are supposed to add your DAX code to create a new table for Date.
Copy and paste the code below into the formula bar and the hit Enter.
dim_Date = ADDCOLUMNS(CALENDAR(MIN(financials[Date]),MAX(financials[Date])),
"Date as integer", FORMAT([Date],"YYYYMMDD"),
"Year", YEAR([Date]), "MonthNo", FORMAT([Date], "MM"),
"YearMonthNo", FORMAT([Date],"YYYY/MM"),
"YearMonth", FORMAT([Date], "YYYY/mmm"),
"MonthShort", FORMAT([Date], "mmm"),
"MonthLong", FORMAT([Date], "mmmm"),
"WeekNo", WEEKDAY([Date]),
"WeekDay", FORMAT([Date], "dddd"),
"WeekDayShort", FORMAT([Date], "ddd"),
"Quarter", "Q" & FORMAT([Date], "Q"),
"YearQuarer", FORMAT([Date],"YYYY") & "/Q" & FORMAT([Date], "Q")
)
What this code is basically telling Power BI to do is to create a new table with dates for every day between a period of first sale and last sale, also add separate columns that identify month, year, quarter, week etc., as seen below from Data view.
If you go back to Model view, you will see that new table has been created named dim_Date (dim stands for dimension tables, just to make your life easier identifying whether it is a fact table or dimension table).
Next step is creating a connection between date in dim_Date table and date in financials table. To do that, simply select Date in dim_Date, drag&drop over Date in finacials table. This will create a one-to-many relationship (read more) and now we will be able to do Time Intelligence analysis as needed.
Please note in the code we used earlier to generate Date table, we used the below which is based on the other data table (financials table in our case) as values for earliest date and latest date are generated from that table.
dim_Date = ADDCOLUMNS(CALENDAR(MIN(financials[Date]),MAX(financials[Date]If you want to create a calendar table but you do not want to specify the period like we did in the previous step earlier you can use the calendarauto() function and this will include that period automatically.
dim_Date = ADDCOLUMNS(CALENDARAUTO()Chances are low that you want to read more, especially because today is Monday blues, but if you feel like you can do more, read Tips and Tricks in Power BI.
That’s it. Happy Monday!