skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


Guest post by David Churchward

Using Time Intelligence in PowerPivot can appear scary when you first start using it and I’ve seen some weird and wonderful ways of attacking it, some that look like we’re calling into question the validity of the global phenomenon that is time and others that are quite simply brilliant.

In this post, I hope to provide a simple and understandable approach whilst adding another flexible dimension called “X” where “X” is a variable that can be set by a user in a slicer.  This might be, for example, a P&L report where the user wants to see the last 4 months instead of the normal pre-determined timeframes such as year to date or quarters.  Alternatively, the report may be reviewing trends and, again, year to date or one of the other standard timeframes may not be the most relevant timeframe.

Incidentally – if you’re in the mood for conquering time intelligence there’s a host of brilliant information on this site including posts such as Running Totals Without a Traditional Calendar Table and PowerPivot time intelligent functions: why use ALL() and how to work around it which give some excellent direction on Time Intelligence and some of the complexities.

In this post, I’ll explain how you can put all of the control in the hands of the user by creating an X Periods measure. That is to say that the user can review the last X periods up to a date that they control.

Paving the Way

The crux of dealing with time intelligence in this way is to create a time table that links to your dataset and then a further time selection set of tables from which the user can select their required time parameters.

I’ve created so many of these where the fiscal year isn’t a conventional calendar year that I’ve found that it’s best to deal with the fact that some of the built-in time intelligence PowerPivot functions may not be valid. However, with last X periods, it doesn’t matter anyway.

I need the following tables in my dataset:

FACT_Tran– this is my fact table dataset that I wish to analyse.

clip_image002

Dates– this is an unbroken list of dates representing the timeframe of my dataset. Time Intelligence works better when your fact table is linked to a dates table, chronologically ordered without any breaks.

clip_image002

Year– a list of years in the dataset

clip_image003

Period – a list of periods in the dataset. This would normally be 1 to 12

clip_image004

Year_Period– this is more aligned to a proper time dimension in that it is a combination of the years and periods representing my dataset and carries additional information such as month end dates that we need for our measures (this version has been simplified for the purposes of this post)

clip_image005

X– this is a single column table that allows users to select the number of periods that they wish to see. This could be a numeric sequential number or specific timeframes that are most likely to be selected as in my example here.

clip_image006

Note – I’m using a predetermined set of values for X which contradicts my opening remarks but this is purely for the purposes of showing this example and it really depends on the situation!

Links

clip_image007

You’ll notice that my Year_Period table is linked to my Year and Period tables. My fact table is separately linked to my Dates table. For this method of Time Intelligence to work, you shouldn’t link the two sets of tables together otherwise filtering will occur when we don’t want it to.  You can get around this, but there’s no need to link the two sets of tables anyway.

Determining what has been Selected

Users will be given slicers to select the year and period that they are working on. In terms of X periods, we would consider this to drive the end date of the period to which X relates.  We then work back X periods from that date.

I need to create a series of measures to determine what the user has selected. These are as follows:

Selected_Month_End_Date = LASTDATE(Year_Period[Month_End_Date])

Selected_X = MAX(X[X])

Selected_X_Months_Start_Date = LASTDATE(DATEADD(Year_Period[Next_Month_Start_Date],[Selected_X]*-1,MONTH))

clip_image008

As I’m using Fiscal Periods and my financial year starts in July, period 3 for the year ending 2010 (as selected above) is actually September 2009. I’ve selected 9 as a value for X which means that I’m looking back 9 months from September 2009. My start date is therefore 1st Jan 2009.

Building These Dates into my Financial Measure

Now that we know what the user has selected in terms of X and the date range to which X relates, our financial measure is now very simple.

X_Periods_Value

=CALCULATE

(

SUM(FACT_Tran[Value]),

DATESBETWEEN(Dates[Date],[Selected_X_Months_Start_Date],[Selected_Month_End_Date])

)

We now have a dynamic measure which calculates the start and end dates that we wish to see and applies those dates to our value column.

This can be massaged to calculate year to date, prior year and many other date values as required and it is totally dynamic without any back end calculations.

Taking Things to the Next Level

The method above is what I use regularly to allow for year, period and X selectors to drive the analysis.  This works a treat if your primary reporting timeframe is a month.  However, what if days or weeks are a relevant timeframe?

Let’s adapt what we’ve already done by creating a new dates table.  Let’s call this table Dates2.  This is a sequential date column similar (if not identical) to Dates but NOT linked to our fact table.  Create 3 new measures on this table which are variations of the Selected_X_Months_Start_Date measure that we created previously.

Selected_X_Days_Start_Date

= DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-1,DAY)),1,DAY)

Selected_X_Weeks_Start_Date

= DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-7,DAY)),1,DAY)

Selected_X_Months_Start_Date

= DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-1,MONTH)),1,DAY)

So why do we need two DATEADD functions in each of these measures? Simply put, we’re essentially using dates here. That’s a simple statement that doesn’t answer anything! What I mean is that we’re essentially not using datetime fields. Each date is therefore a value in it’s own right. Therefore, if we treat 30th September as a day only and therefore a value of 30, when we subtract one day, we would get 29. As a result, we would be reviewing 29th and 30th when in reality we only want to review one day. Since 30th is one day in it’s own right, we always have to add one day to each answer.

Note – take care to ensure that your fact table dates are in the same format.  Time can skew the answer if you’re not careful.

In the previous solution using year and period slicers to select months above, this problem was dealt with by using the Next_Month_Start_Date value which is one day ahead of our month end date selected and held as a value in our Year_Period table.

Our weeks solution is a derivative of the days function where we simply adjust by days * 7 to get to weeks.

I now create another selection table called Time_Type.  This is designed to select which timeframe type the user is selecting.

image

I create a slicer on my pivot table using the Type_Name and create a measure which determines which item has been selected by the user.

Selected_TimeType

= MAX(TimeType[Type_Code])

I then create another measure which uses the selected time type to determine which of the 3 measures to use:

Selected_X_TimeType_Start_Date

= IF([Selected_TimeType]=1,[Selected_X_Days_Start_Date],

IF([Selected_TimeType]=2,[Selected_X_Weeks_Start_Date],

IF([Selected_TimeType]=3,[Selected_X_Months_Start_Date],

   BLANK()

  )

   )

     )

image

We need one further measure to determine which date has been selected

Selected_Date2

= LASTDATE(Dates2[Date])

These new measures can now be used in our value measure:

X_Periods_Value

=CALCULATE

(

SUM(FACT_Tran[Value]),

DATESBETWEEN(Dates[Date],[Selected_X_TimeType_Start_Date],[Selected_Date2])

)

What if I Always Want my Report to be X Periods to Today’s Date?

This is simple.  We simply need to substitute Selected_Date2 for a new measure called Todays_Date in our X_Periods_Value measure and substitute Dates2[Date] with Todays_Date in Selected_X_Days_Start_Date and Selected_X_Weeks_Start_Date and Selected_X_Months_Start_Date measures

Todays_Date

= TODAY()

You do however need to take care about what your data content is.  As an example, I use a data warehouse that is built every night.  When I refer to today, I actually mean last night which means that my measure should in fact be

Todays_Date_LastNight

= TODAY() -1

What Does This Mean?

By using this method, we have passed the requirement to determine the timeframe to which a trend or report relates across to the user.  Trend timeframes could change and do we really want to have to recreate reports and dashboards as a result?  I hope that this solution solves that problem.

Having learnt from my previous post, YOU CAN DOWNLOAD THE WORKBOOK HERE.

This Post Has 10 Comments
  1. Thanks for posting this. I did notice that the formula for the measure Selected_X_Months_Start_Date in the workbook does not match the definition provided above. I replaced it with:

    Selected_X_Months_Start_Date
    = DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-1,MONTH)),1,DAY)

  2. Excellent tutorial

    One note – your measure for months

    Selected_X_Months_Start_Date
    = DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-1,MONTH)),1,DAY)

    does not return the start date but the end of the previous month …
    I have just added another day.

    Selected_X_Months_Start_Date
    = DATEADD(LASTDATE(DATEADD(Dates2[Date],[Selected_X]*-1,MONTH)),2,DAY)

  3. This is great. One more question – how can I reuse this pattern if I want to get all the months between start date and end date listed, month by month? For example to show the chart with monthly evolution in the selected period of x months.

  4. I was trying to recreate this but I get the following error: MdxScript(Model) (6,60) Calculation error in measure ‘DateTable[Slected_X_Startmonth]: Function ‘Dateadd’ only works with contigous date selections. What did I do wrong?

    1. I would check the data in your Date table – it sounds like maybe you have some missing dates. For example, 08/01/2016, 08/02/2016, 08/05/2016, 08/06/2016 – In this case the Date table would be missing the date(s) 08/03/2016 & 08/04/2016.

      Dates– this is an unbroken list of dates representing the timeframe of my dataset. Time Intelligence works better when your fact table is linked to a dates table, chronologically ordered without any breaks.

      Also, check to make sure you have the Date table set as “Mark as Date Table” with the correct column representing the consecutive date. This setting is located in the Power Pivot, Manage – then the Design menu options.

    1. Have you looked at documentation for Time Intelligence?

      https://support.office.com/en-us/article/Time-Intelligence-in-Power-Pivot-in-Excel-016acf7b-9ded-411e-ba6c-ed8b8c368011

      i.e.> check PREVIOUSYEAR

      The following sample formula creates a measure that calculates the previous year sales for the Internet sales.

      =CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSYEAR(‘DateTime'[DateKey]))

      To see how this works, create a PivotTable and add the fields, CalendarYear and CalendarQuarter, to the Row Labels area of the PivotTable. Then add a measure, named Previous Year Sales, using the formula defined in the code section, to the Values area of the PivotTable.

  5. Hi David, I have been enjoying and learning a lot from your articles. Thank you so much for doing such an outstanding job in explaining the steps involved and the accuracy of all your examples. I ran into a problem with a time slicer that I based on your article. The time slicer is based on calendar month periods (i.e. 1 to 12). If I select odd months, it works fine, but if I select even ones, it tallies all data from the beginning of the year. I ran out of ideas trying different solutions. Any suggestions will be greatly appreciated as I am very new to using power pivoting.

Leave a Reply

Your email address will not be published. Required fields are marked *