From DAX to SQL: Replicating the Essential Date Table for BI Developers
Having a date table is not optional when it comes to Business Intelligence. When building data models, it’s actually the first table I’m adding most of the time.
Why using a date table?
As a BI developer, I need to filter different dates from my facts tables either on the front or the back.
On the front side, softwares like Tableau already have great date filtering possibilities : https://help.tableau.com/current/pro/desktop/en-us/qs_relative_dates.htm
As shown below, you can select a relative date with Tableau, and select the current week, the last 3 weeks, months, quarters, etc…
When possible, I think it’s best to use out of the box date filtering possibilities.
On the back side, in SQL for example, it makes a lot of sense to have a shared date table across developers to be efficient.
If you want to filter a fact table on the last 3 weeks for example, you simply need to join your fact table with your date table, and add the following condition:
WHERE relative_week_pos >=-3
AND relative_week_pos <1
AND relative_year_pos = 0
From DAX to SQL
There’s a great date table template provided by SQLBI that you can find here : https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/ . However, this tool isn’t suited for companies with several people working on Business Intelligence. As the date table above is coded in DAX, it sits locally on a PBI file. Even if all BI developers use this DAX code to begin their data models, it’s risky and definitely not “best practice”.
The better solution is to reproduce this date table in a data warehouse so all BI developers can get the table in a click in their Power BI model. The date template provided above is a very good starting point because it has some parameters, a very good naming convention and is well documented.
Here’s the SQL query I’ve coded in Databricks to reproduce most of the existing table on SQLBI. I didn’t bother with holidays because I don’t need it at the moment, but it’s obviously possible to add the logic with SQL.
The SQL date table
/*
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
The following examples are obtained and described using March 21, 2018 as a reference.
*/
create or replace table dim_date as
with cte_1 as (
select day_id_day as date, -- This is the date in Date data type
current_date() as today_reference, -- today's date
1 as first_day_of_week, -- use: 0 - sunday, 1 - monday, 2 - tuesday, +... 5 - friday, 6 - saturday
make_date(1900, 12, 30) as first_sunday_reference
from table_with_days
),
cte_2 as (
select *,
first_sunday_reference + first_day_of_week as first_week_reference,
year(today_reference) as current_calendar_year_pos,
day(date) as day_of_month, -- 21 (Integer) – Day of the month
year(date) * 10000 + month(date) * 100 + day(date) as date_key, -- 20180321 (Integer) – Date in integer format YYYYMMDD
weekday (date) + first_day_of_week as week_day_number, -- 3 (Integer) – Day of the week in numeric format, where 1 is the first day of the week (depends on configuration parameters)
date_format(date, 'EEEE') as week_day, -- Tueqsday (String) – Name of the day of the week
year(date) as year_number, -- 2018 (Integer) – Calendar year number
year(date) as year, -- 2018 (String) – Calendar year, sorted by Calendar YearNumber
weekofyear(date) as week_number, -- 12 (Integer) – Calendar week number
ceil (month(date) / 3, 0) as quarter_number, -- 1 (Integer) – Calendar quarter number
month(date) as month_number, -- 3 (Integer) – Calendar month number, where 1 is January
date_format(date, 'MMMM') as month_name, -- March (String) – Calendar month name, sorted by Calendar MonthNumber
year(date) * 12 - 1 + month(date) as year_month_number, -- 24218 (Integer) – Sequential month number across years (= [Calendar YearNumber] * 12 + [Calendar MonthNumber] – 1)
make_date(year(date), month(date), 1) as start_of_month, -- 3/1/2018 (Date) – First day of the month
last_day(date) as end_of_month, -- 3/31/2018 (Date) – Last day of the month
to_date(date_trunc('quarter', date), 'yyyy-mm-dd') as start_of_quarter, -- 1/1/2018 (Date) – First day of the quarter
make_date(year(date), 1, 1) as start_of_year, -- 1/1/2018 (Date) – First day of the year
make_date(year(date), 12, 31) as end_of_year -- 12/31/2018 (Date) – Last day of the year
from cte_1
order by date_key ASC
),
cte_3 as (
select *,
round (
datediff(today_reference, first_week_reference) / 7
) as current_week_pos,
current_calendar_year_pos * 12 - 1 + month(today_reference)
as current_month_pos,
current_calendar_year_pos * 4 - 1 + ceil (month(today_reference) / 3, 0)
as current_quarter_pos,
concat('W', to_char (week_number, '00')) as week, -- W12 (String) – Calendar week, sorted by Calendar WeekNumber
concat('Q', quarter_number) as quarter, -- Q1 (String) – Calendar quarter, sorted by Calendar QuarterNumber
CAST (round (datediff(date, first_week_reference) / 7) as integer)
as year_week_number, -- 6116 (Integer) – Sequential year number across years. A week always counts 7 days.
-- When a week crosses over from December into January, same week number is used on both sides.
year(date) * 4 - 1 + quarter_number as year_quarter_number, -- 8072 (Integer) – Sequential quarter number across years (= [Calendar YearNumber] * 4 + [Calendar QuarterNumber] – 1)
year(date) * 100 + week_number as week_year_order, -- 201812 (Integer) – Calendar year and week in YYYYWW format (a calendar week can have less than 7 days at start and end of year)
concat('Q', quarter_number, ' ', year(date)) as quarter_year, -- Q1 2018 (String) – Quarter and year, sorted by Calendar YearQuarterNumber
year(date) - current_calendar_year_pos as relative_year_pos, -- 0 (Integer) – Relative years compared to “TodayReference” (see parameters); negative values are for years before TodayReference,
-- positive values are for years after TodayReference. 0 implies same year as TodayReference
last_day(make_date(year(date), month(start_of_quarter) + 2, 1))
as end_of_quarter, -- 3/31/2018 (Date) – Last day of the quarter
day_of_month as day_of_month_number, -- 21 (Integer) – Sequential number of the day within the month (it is like the Day of Month column)
datediff(date, start_of_quarter) + 1 as day_of_quarter_number, -- 79 (Integer) – Sequential number of the day within the quarter
datediff(date, start_of_year) + 1 as day_of_year_number, -- 79 (Integer) – Sequential number of the day within the year
datediff(end_of_month, start_of_month) + 1 as month_days, -- 31 (Integer) – Number of days in the month
datediff(end_of_year, start_of_year) + 1 as year_days, -- 365 (Integer) – Number of days in the year
concat(month_name, ' ', year(date)) as month_year, -- March 2018 (String) – Month and year, sorted by Calendar YearMonthNumber
concat(week_number, '-', year(date)) as week_year -- W12-2018 (String) – Calendar week and year, sorted by Calendar WeekYearOrder
-- (a calendar week can have less than 7 days at start and end of year)
from cte_2
),
cte_4 as (
select *,
CAST (round (datediff(date, first_week_reference) / 7) - current_week_pos as integer)
as relative_week_pos, -- -8 (Integer) – Relative weeks compared to “TodayReference” (see parameters);
-- negative values are for weeks before TodayReference, positive values are for weeks after TodayReference
year(date) * 12 - 1 + month(date) - current_month_pos as relative_month_pos,-- -2 (Integer) – Relative months compared to “TodayReference” (see parameters);
-- negative values are for months before TodayReference, positive values are for months after TodayReference
year_quarter_number - current_quarter_pos as relative_quarter_pos, -- 0 (Integer) – Relative quarters compared to “TodayReference” (see parameters);
-- negative values are for quarters before TodayReference, positive values are for quarters after TodayReference. 0 implies same quarter as TodayReference
datediff(end_of_quarter, start_of_quarter) + 1 as quarter_days -- 90 (Integer) – Number of days in the quarter
from cte_3
)
select
/*
first_day_of_week,
first_sunday_reference,
first_week_reference,
current_calendar_year_pos,
current_quarter_pos,
current_month_pos,*/
date,
today_reference,
day_of_month,
date_key,
week_day_number,
week_day,
year_number,
year,
week_number,
week,
month_number,
month_name,
quarter_number,
quarter,
year_week_number,
year_month_number,
year_quarter_number,
week_year,
week_year_order,
month_year,
quarter_year,
relative_week_pos,
relative_month_pos,
relative_quarter_pos,
relative_year_pos,
start_of_month,
end_of_month,
start_of_quarter,
end_of_quarter,
start_of_year,
end_of_year,
day_of_month_number,
day_of_quarter_number,
day_of_year_number,
month_days,
quarter_days,
year_days
from cte_4
