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.