As you progress in Power BI (and Power Pivot), you quickly find you need a date dimension, the problem is that date dimensions are difficult to build and even more difficult if you want them to be more than just a list of dates.
“Stop I’m confused… how can a list of dates be more?”
The role of a good date dimension is indeed to be more than just a list of dates, there are functions in Power BI to produce a list of dates, the issue is that you want something that is more. I’ll be honest at this point before we dive into the queries, I use, I cannot remember where I got the original source, I found three or four different date dimension SQL queries initially and then started pulling them together and adjusting them to fit my needs. For me we were going to have the majority of our data coming from a SQL server so it makes sense to have a SQL date dimension, based on what we did and after doing some benchmarking within Power BI I would recommend housing it on a Database, putting it in Power BI or excel won’t stop things working, but I have got best results from this method.
The important thing about the first bit is that there are many, many scripts out there to do what you need. Typically they have the same format.
I’ll make these source files available to download
Populate Dates Dimension
- Deal with 1 character days and months
- Set Fiscal Year Months – where I work we use August to July so I needed to add a case statement here, case statements sound more complex than they are
- Set Fiscal Year
- Set Fiscal quarter
- Set Fiscal week
“I’m waiting I hear you say, so far so dull and boring, what makes you so different from all the rest of the people out there?”
Step 8 give your date dimension time intelligence!
What my team and I realised was that so often we were writing reports about “this month” or “last month” or “this year” or “last year”, what was needed was a way to make these reports automatic, so –eventually – I created a view that would define “Today” and from there work out the future and the past in a straight forward manner.
Create View v_Dim_Date AS SELECT ID, Date, Day, DaySuffix, DayOfWeek, Month, MonthName, Year, StandardDate, FY_Month, FY_Text, FY_Week, FY_Q, FY_QName, MonthYearKey, MonthYear, SMonth, WDnumber, DATEDIFF(DAY,getdate(),d.date) as DayDrift, DATEDIFF(WEEK,GETDATE(),DATEADD(dd,-(datepart(dw,d.date)-1),d.date)) as WeekDrift, DATEDIFF(Month, getdate(),d.date) as monthdrift, CASE WHEN MONTH(d.date) > 7 THEN CASE WHEN MONTH(d.date) > 7 THEN DATEDIFF(Year,Getdate(),d.date)+1 ELSE DATEDIFF(Year,Getdate(),d.date) END as FYDrift, convert(date, DATEADD(dd,-(datepart(dw,d.date)-1),d.date)) [WeekStart], CONVERT(Date, DATEADD(dd, 7-(DATEPART(dw,d.date)),d.date)) [WeekEnd], Cast(convert(date, DATEADD(dd,-(datepart(dw,d.date)-1),d.date)) as varchar) +' - '+ cast(CONVERT(Date, DATEADD(dd, 7-(DATEPART(dw,d.date)),d.date))as varchar) [Range], d.FY_Text +'-'+ d.FY_Week [WeekReport] FROM DIM_Date AS d
We now have everything that a date aware report could need
DayDrift – How many days ahead or behind today. Today = 0
WeekDrift – How many weeks ahead or behind today. This week = 0
MonthDrift – How many months ahead or behind today. This month = 0
FYDrift – How many fiscal years ahead or behind today. This FY = 0
WeekStart – first day of the week
WeekEnd – last day of the week
Range – Formatted week range (for charts etc…)
Suddenly our reporting is simplified, start a new report, drop into the report filter FYDrift = 0 (this year) or MonthDrift =-1 (last month), with the context there is also so much that can be done, we have I feel hardly touched the surface!