cancel
Showing results for 
Search instead for 
Did you mean: 
amitchandak

Power BI- Direct Query: Date Table in SQL Server

Problem Statement:

A date table is needed in the Direct Query at the source. The source is SQL Server database

 

Solution:

In order to create a date table, SQL will be used

 

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
end

 

With 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