Power BI- Direct Query: Date Table in SQL Server
If you use power bi direct query, you can define a date table at the source or in power bi. You can create a date table in Power BI. The table created in Power BI is in import mode. Power BI will enable mixed-mode queries.
You can refer to my previous blogs to create the Power BI Date Calendar in DAX or Power Query.
In this article, I will demonstrate how to create a date table in SQL Server. This can be achieved by using the function generate_series which is available to you if you have SQL Server compatibility level 160 or higher. You can make use of this function in this case. However, many of you may not have that function yet. So I decided to take the looping approach.
In this case, I will use
While Loop — Loop dates (number)
DATEDIFF- to get the duration in days for the loop
DATEADD- add duration to a date
EOMONTH — to find the month-end date. You can use both positive and negative integers to move in either direction
FORMAT — format the date
DATEPART — to get the various date parts like a week, quarter, etc
DENSE_RANK- create a rank on year week
There are a few of these functions that appear to be very similar to power bi, but their syntax may be entirely different from power bi.
First, create a date table using this script
Create Table date_table(
[Date] datetime,
month_start_date datetime,
month_end_date datetime,
month_year varchar(255),
year_month int,
month_number int,
qtr_start_date datetime,
qtr_end_date datetime,
qtr varchar(255),
qtr_year varchar(255),
year_qtr int,
qtr_no int,
year_start_date datetime,
year_end_date datetime,
year int,
weekday int,
weekday_name varchar(255),
week_start_date datetime,
week_end_date datetime,
weeknum int,
year_week int,
year_week_rank int
);Using this anonymous procedure, load the data
DECLARE @Counter INT, @StartDate Date, @EndDate Date,@Days int
SET @StartDate='2020-01-01'
SET @EndDate='2022-12-01'
SET @Counter=0
SET @Days= datediff(day,@StartDate,@EndDate)
Begin
truncate table date_table;
WHILE ( @Counter <= @Days)
BEGIN
insert into date_table([Date] ,
month_start_date ,
month_end_date ,
month_year ,
year_month ,
month_number ,
qtr_start_date ,
qtr_end_date ,
qtr ,
qtr_year ,
year_qtr ,
qtr_no ,
year_start_date ,
year_end_date ,
year ,
weekday ,
weekday_name ,
week_start_date ,
week_end_date ,
weeknum ,
year_week ,
year_week_rank)
select [Date]
, DATEADD(DAY,1,EOMONTH([Date],-1)) month_start_date
, EOMONTH([Date],0) month_end_date
, format([Date],'MMM-yyyy') month_year
, year([Date]) *100 + month([Date]) year_month
, month([Date]) month_number
, DATEADD(DAY,1,EOMONTH([Date],-1* (case when month([Date])%3=0 then 3 else month([Date])%3 end ) )) qtr_start_date
, EOMONTH([Date], (case when month([Date])%3=0 then 0 else (3- (month([Date])%3)) end ) ) qtr_end_date
, 'Q' + cast( DATEPART(Q,[Date]) as varchar) qtr
, 'Q' + cast( DATEPART(Q,[Date]) as varchar) +'-' + cast( year([Date]) as varchar) qtr_year
, year([Date]) *100 + DATEPART(Q,[Date]) qtr_year
,DATEPART(Q,[Date]) qtr_no
, DATEADD(DAY,1,EOMONTH([Date],-1* (month([Date]) ))) year_start_date
, EOMONTH([Date],12-1* (month([Date]) ))year_end_date
, year([Date]) year
, DATEPART(dw,[Date]) weekday
, format([Date],'ddd') weekday_name
, DATEADD(day,-1*DATEPART(dw,[Date])+1,[Date]) week_start_date
, DATEADD(day,-1*DATEPART(dw,[Date])+7,[Date]) week_end_date
, DATEPART(wk,[Date]) weeknum
, year([Date])*100+ DATEPART(wk,[Date]) year_week
, dense_rank() over(order by (year([Date])*100+ DATEPART(wk,[Date]))) year_week_rank
from (
select DATEADD(DAY,@Counter,@StartDate) Date ) A1
SET @Counter = @Counter + 1
END
endWith generate_series, the code will look like this:
DECLARE @Counter INT, @StartDate Date, @EndDate Date,@Days int
SET @StartDate='2020-01-01'
SET @EndDate='2022-12-01'
SET @Counter=0
SET @Days= datediff(day,@StartDate,@EndDate)
Begin
truncate table date_table;
insert into date_table([Date] ,
month_start_date ,
month_end_date ,
month_year ,
year_month ,
month_number ,
qtr_start_date ,
qtr_end_date ,
qtr ,
qtr_year ,
year_qtr ,
qtr_no ,
year_start_date ,
year_end_date ,
year ,
weekday ,
weekday_name ,
week_start_date ,
week_end_date ,
weeknum ,
year_week ,
year_week_rank)
select [Date]
, DATEADD(DAY,1,EOMONTH([Date],-1)) month_start_date
, EOMONTH([Date],0) month_end_date
, format([Date],'MMM-yyyy') month_year
, year([Date]) *100 + month([Date]) year_month
, month([Date]) month_number
, DATEADD(DAY,1,EOMONTH([Date],-1* (case when month([Date])%3=0 then 3 else month([Date])%3 end ) )) qtr_start_date
, EOMONTH([Date], (case when month([Date])%3=0 then 0 else (3- (month([Date])%3)) end ) ) qtr_end_date
, 'Q' + cast( DATEPART(Q,[Date]) as varchar) qtr
, 'Q' + cast( DATEPART(Q,[Date]) as varchar) +'-' + cast( year([Date]) as varchar) qtr_year
, year([Date]) *100 + DATEPART(Q,[Date]) qtr_year
,DATEPART(Q,[Date]) qtr_no
, DATEADD(DAY,1,EOMONTH([Date],-1* (month([Date]) ))) year_start_date
, EOMONTH([Date],12-1* (month([Date]) ))year_end_date
, year([Date]) year
, DATEPART(dw,[Date]) weekday
, format([Date],'ddd') weekday_name
, DATEADD(day,-1*DATEPART(dw,[Date])+1,[Date]) week_start_date
, DATEADD(day,-1*DATEPART(dw,[Date])+7,[Date]) week_end_date
, DATEPART(wk,[Date]) weeknum
, year([Date])*100+ DATEPART(wk,[Date]) year_week
, dense_rank() over(order by (year([Date])*100+ DATEPART(wk,[Date]))) year_week_rank
from (
select DATEADD(DAY,GENERATE_SERIES(@Counter,@Days,1),@StartDate) A;
end My Medium blog can be found here if you are interested
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
There is a Power BI tutorial series that you can watch on my channel. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
