Using Query Editor to Create a Time Table

Guest Post by David Churchwardwith Technical Input from Scott Senkeresty

The first thing I ever do when I create a new PowerPivot workbook is create the date tables that I need.  Until now, I’ve used a preconditioned SQL table to draw this information from.  Now, thanks to Scott Senkeresty, you can now create your own – from PowerPivot!

Scott is a true genius.  He spends a lot of time under the hood of Pivotstream operations working his magic and wizardry and, every now and then, he pops up for air to deliver a masterpiece like this.

It’s as Simple as Copy and Paste

If you read my last post SQL UNPIVOT Makes My Data Skinny, you’ll know where query editor is and the sort of tricks that it opens up within PowerPivot.  In this post, there’s some SQL scripts that create time tables and you can simply cut and paste them into the query editor and use them straight away.

Having said this, it’s worth checking out Rob’s post The Ultimate Date Table to see why a date table is important and where you can get one of your own from Azure DataMarket.  However, I think we’ve gone one step better now and given you a script that you can parameterise easily, condition relatively easily and setup extremely easily.  Add to that the fact that it runs in mere seconds!

AND there’s one more bonus, especially for Accountants, you can set this up for YOUR Fiscal Year!

Copy, Paste, Save – Simples!

To get going, simply copy and paste the script from THIS ATTACHMENT into query editor.  When you save it – bam – time table complete.

DECLARE @FirstDate DATE

DECLARE @LastDate DATE

SET @FirstDate ='1 Jan 2008'-- Enter first date of calendar as 'dd mmm yyyy'

SET @LastDate ='31 Dec 2015'-- Enter last date of calendar as 'dd mmm yyyy'

IF @FirstDate ISNULLOR @FirstDate =''

                BEGIN

                                SET @FirstDate ='1 Jan 2000'

                END

IF @LastDate ISNULLOR @LastDate =''

                OR @LastDate < @FirstDate

                ORDATEDIFF(DAY, @FirstDate, @LastDate)> 10000

                BEGIN

                                SET @LastDate =DATEADD(DAY, 10000, @FirstDate)

                END

                ;WITH CTE_DatesTable

                AS

                (

                  SELECT @FirstDate AS [Date]

         UNIONALL

         SELECTDATEADD(DAY, 1, [Date])

         FROM CTE_DatesTable

         WHEREDATEADD(DAY, 1, [Date])<= @LastDate

                )

                SELECT

                                Date,

                                DATEPART(DAYOFYEAR,Date)                             ASDayOfYear,

                                DATEPART(DAY,Date)                                          ASDay,

                                DATEPART(MONTH,Date)                                    ASMonth,

                                DATEPART(YEAR,Date)                                        ASYear,

                                DATENAME(WEEKDAY,Date)                               ASDayOfWeek,

                                LEFT(DATENAME(WEEKDAY,Date),3)                 AS DayOfWeekAbr,

                                DATEPART(WEEK,Date)                                       AS WeekOfYear,

                                DATEPART(QUARTER,Date)                                 ASQuarter,

                                DATENAME(MONTH,Date)                                  ASMonthName,

                                LEFT(DATENAME(MM,Date),3)                           AS MonthNameAbr,

                                DATEADD(DAY,-1,DATEADD(MONTH, 1,DATEADD(DAY,(DATEPART(DAY,

DATE)*-1)+1,Date)))                         AS MonthEndDate

                FROM

                CTE_DatesTable

                OPTION (MAXRECURSION 10000)

Without doing anything to this script, you’ll get a time table that spans Jan 2008 to Dec 2015.  That could be enough.  In which case, you’re done.  However, I’m sure you want more.

Change Date Parameters to Widen the Timeframe

The great news with this script is that it’s so easy to adjust to cover the timeframe that you need.  You just need to follow these simple couple of steps:

  1. Towards the top of the script you’ll see “SET @FirstDate = “.  You simply need to change the value to the first date in your dataset (or perhaps an earlier date to be sure).
  2. I’m sure you’ve already guessed step 2 – change “SET @LastDate = “ to a value that is beyond the last date in your dataset.

I almost wish I could make it more difficult but Scott’s nailed this so well that it’s almost impossible to get wrong – well almost!

A Quick Health Warning

This script uses a recursive CTE (Common Table Expression).  If you’re not careful, it’s very possible to spin this baby into an infinite cycle.  Without going into why this happens, here’s a couple of points to make sure that you don’t fall into that trap:

  1. The IF – BEGIN – SET – END statements are there to ensure that valid entries are made for @FirstDate and @LastDate.  It’s probably best to leave these in.
  2. At the end of the script, there’s a command OPTION(MAXRECURSION 10000).  This ensures that the script won’t loop more than 10,000 times (ie 10,000 days).  If you set the recursion to zero, this removes all limits and then you could get into an infinite cycle.

Don’t be scared to give it a go.  There’s controls here to stop anything going wrong and what’s the worst that could happen anyway!

The Next Level – Fiscal Elements

In all my time using SQL and PowerPivot, I’m yet to work in an environment where a year runs from 1st January to 31st December.  As a result, we refer to a Fiscal Year to represent this alternate calendar.  I took the liberty of hacking Scott’s script to add these elements in.

DECLARE @FirstDate DATE

DECLARE @LastDate DATE

DECLARE @FiscalStart INT

SET @FirstDate ='1 Jan 2008'-- Enter first date of calendar as 'dd mmm yyyy'

SET @LastDate ='31 Dec 2015'-- Enter last date of calendar as 'dd mmm yyyy'

SET @FiscalStart = 4 -- First calendar period of Fical Year - 1=Jan, 2=Feb...., 12=Dec

IF @FirstDate ISNULLOR @FirstDate =''

                BEGIN

                                SET @FirstDate ='1 Jan 2000'

                END

IF @LastDate ISNULLOR @LastDate =''

                OR @LastDate < @FirstDate

                ORDATEDIFF(DAY, @FirstDate, @LastDate)> 10000

                BEGIN

                                SET @LastDate =DATEADD(DAY, 10000, @FirstDate)

                END

                ;WITH CTE_DatesTable

                AS

                (

                  SELECT @FirstDate AS [Date]

                 UNIONALL

                 SELECTDATEADD(DAY, 1, [Date])

                 FROM CTE_DatesTable

                 WHEREDATEADD(DAY, 1, [Date])<= @LastDate

                )

                SELECT

                                Date,

                                DATEPART(DAYOFYEAR,Date)                             ASDayOfYear,

                                DATEPART(DAY,Date)                                          ASDay,

                                DATEPART(MONTH,Date)                                    ASMonth,

                                DATEPART(YEAR,Date)                                        ASYear,

                                DATENAME(WEEKDAY,Date)                               ASDayOfWeek,

                                LEFT(DATENAME(WEEKDAY,Date),3)                 AS DayOfWeekAbr,

                                DATEPART(WEEK,Date)                                       AS WeekOfYear,

                                DATEPART(QUARTER,Date)                                 ASQuarter,

                                DATENAME(MONTH,Date)                                  ASMonthName,

                                LEFT(DATENAME(MM,Date),3)                           AS MonthNameAbr,

                                DATEADD(DAY,-1,DATEADD(MONTH, 1,DATEADD(DAY,(DATEPART(DAY,

DATE)*-1)+1,Date)))                         AS MonthEndDate,

                                DATEADD(DAY,-1,DATEADD(MONTH, 1 + 12 -(DATEPART(MONTH,DATE)

- @FiscalStart + 1 +CASEWHEN @FiscalStart >DATEPART(MONTH,DATE)

THEN 12 ELSE 0 END),DATEADD(DAY,(DATEPART(DAY,DATE)*-1)+1,

Date)))                                                   AS YearEndDate,

                                DATEPART(MONTH,DATE)- @FiscalStart + 1 +

                                                CASEWHEN @FiscalStart >DATEPART(MONTH,DATE)THEN 12

ELSE 0 END                                             AS FiscalPeriod,

                                YEAR(DATEADD(DAY,-1,DATEADD(MONTH, 1 + 12 -(DATEPART(MONTH,

DATE)- @FiscalStart + 1 +CASEWHEN @FiscalStart >

DATEPART(MONTH,DATE)THEN 12 ELSE 0 END),DATEADD(DAY,

(DATEPART(DAY,DATE)*-1)+1,Date))))

AS FiscalYearEnd,

                                CASEDATEPART(MONTH,DATE)- @FiscalStart + 1 +CASEWHEN

@FiscalStart >DATEPART(MONTH,DATE)THEN 12 ELSE 0 END

                                                                WHEN 1 THEN 1 

WHEN 2 THEN 1 

WHEN 3 THEN 1

                                                                WHEN 4 THEN 2 

WHEN 5 THEN 2 

WHEN 6 THEN 2

                                                                WHEN 7 THEN 3 

WHEN 8 THEN 3 

WHEN 9 THEN 3

                                                                WHEN 10 THEN 4 

WHEN 11 THEN 4 

WHEN 12 THEN 4

                                                                END                                         AS FiscalQuarter

                FROM

                CTE_DatesTable

                OPTION (MAXRECURSION 10000)

Again, you can simply copy and paste the script from THIS ATTACHMENT into query editor.  However, you need to set a parameter to tell the script when your fiscal year starts.  Towards the top of the script, you’ll see a command “SET @FiscalStart = “ and you need to enter a month number from 1 to 12 where 1 is January through to 12 being December.  Therefore, if the first month of your financial year is April, set this value to 4.  Otherwise, the script works the same as the previous example but returns some additional Fiscal Time elements.

And it’s as simple as that!  Thanks Scott – truly brilliant!

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Siloed and Slow: What Happens When You Aren’t Using Power BI’s Data Model “Brain”

Last week’s post left off here:  most BI still relies on the

Read the Blog

Power Query (M)agic: Consecutive Days

Hello P3 Adaptive World! My name is Krissy Dyess and I have

Read the Blog

Coffee Talk: The PowerON Pros

Welcome to the latest P3 Adaptive Coffee Talk, where members of the

Read the Blog

The Ten Pitfalls of the Data Wrangler (aka the Wielder of M/Power Query)

Ten Pitfalls of the wrangling of using Power Query and how to

Read the Blog