My Ultimate Date Dimension! at least until I figure out a better one….

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

  1. Define Table
  2. Populate Dates Dimension
  3. Deal with 1 character days and months
  4. 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
  5. Set Fiscal Year
  6. Set Fiscal quarter
  7. Set Fiscal week

Read more

Upwards and Onward my friend

So you need to do a comparison across time periods, this has always slightly confused me I’ll be honest. Earlier though I had to figure it out, basically the requirement was to work out what happen in the last month and in the last three months. The idea being to understand if things are improving over time or (please don’t be the case) getting worse.

Now with all the time intelligence that’s out there in Power BI it is really easy to do much of this and DATESINPERIOD() is a real friend. I will no doubt end up adding another tag for Time Intelligence formulae or methods because they really are the ultimate glue that holds most of Power BI or PowerPivot together. Let’s face it half of the time you want to compare things over time don’t you?

DATESINPERIOD(<Possible Date Values (column of dates)>,<Context Date for reference>,<periods for range>, <Period selection>

with Time intelligence it is really important to make sure that you have a dates table. Recent advances in Power BI mean your date table can be much simpler, but you still need to think about what exists in that table and what features are a must from there.

The context date is really the pivot around which Dates in period works I don’t want to call it a start date because you can go forward in time from there or backwards it is the starting point, but I find that simplistic.

Periods are the key here, firstly how many if that number is positive then you will count on that many periods from the context date if it is negative you will be counting backwards from the context date. Possible periods are Day, Month, Quarter, Year

So let us see a sample

=Calculate([Measure],DATESINPERIOD(Dim_Calendar[Date],[SpecificDate],-3,MONTH)

So break this down…

  1. Calculate – Basically work this all out and a return a single (scalar) value
  2. [Measure] – What are you actually wanting to do, you could call an existing measure or write one here
  3. DATESINPERIOD(Dim_Calendar[Date] – return a list of dates from the column
  4. [SpecificDate] – Context value a date that will be used to reference the periods against
  5. -3, MONTH – Month refers to the period and a count of those in this case -3 so go back three month

The clever bit for me was to get the [SpecificDate]

=ENDOFMONTH(DATEADD(Dim_Calendar[Date],-1,MONTH)

What was that?

Break it down, basically it returns the last day of the previous month (with data), typically you run this against a transaction table to try and get the correct context, but I’m sure you could run it against your Dim_calendar table, I just did it differently in this example what are we doing

  • Find the single value (scalar) for the last date in the column
  • Filter the column to be a month ago

The worst possible thing about time intelligence is it mostly isn’t compatible with Direct Query.