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


 
“Because every good story has a beginning, a middle, and an end.”

Awhile back I posted about promotional campaign analysis factoring in seasonal trends.  Now let’s look at another flavor of campaign analysis:  comparing results when a campaign is active versus when it was not.  Sometimes this is referred to as “A/B Testing.”

Let’s start by showing what the results can look like:  a report that has two date slicers – one where you select the Start Date of a campaign, and another where you select the End date:

image Pick Start and End Date, See How Sales Performed On vs. Off Program

The report then shows Sales results “On Program,” which are the sales that occurred between the start date and end date (inclusive) versus the Sales results “Off Program” (sales on all other dates).

Specifically, it shows Sales per Day for On vs. Off Program (because programs run for short periods of time, Sales per Day is a much more “apples to apples” comparison than Total Sales), and then the % change in Sales per Day when On Program versus Off (labeled “Program Delta” above).

(From a quick glance at Program Delta, it’s obvious that this was one awful program that ran from 5/20/03 to 7/8/03, but hey, that’s what we get when we use AdventureWorks as our data set).

Oh, and guess what?  Writing this blog post consumed a lot more time than building the report 🙂

How This Was Built, Step One:  Date Slicers and Date Measures

The first things you need are two single-column tables of dates – these are used to populate the Start and End date slicers:

 image   image

These tables are NOT related to ANY other tables in the model.  They stand alone, intentionally.  Also, MAKE SURE THEY ARE OF DATA TYPE DATE!  Otherwise the following steps will give you strange results.  Also, make sure none of the date columns in your model have time components lurking in them.

OK, now you need to define a measure on each of those two tables.  It’s the same formula, but I name one [Start Date] and the other [End Date], and assign each one to a different table:

image
image

By the way, the technique I’m showing here is a variation of a technique covered in prior blog posts:  one by Kasper and one by me.  Read Kasper’s in particular if I’m moving a little too fast for your taste in this post.

For grins (and to test this out), you can now slap both slicers and both measures on a pivot and inspect what each of those measures returns:

image

We’ll never place those measures on an actual report, but it’s good to see that they serve their purpose, which is to capture the dates that the user selects on the two slicers.  Note that I changed the captions on each slicer to reflect what their intended use is clear (originally they both just had “Date” as a caption since that was the column name in each table).

Step Two:  Sales Measures that are filtered by those Date Measures

Assuming we already have our base [Sales] measure defined, let’s start with [Sales on Program]:

[Sales on Program] = [Sales](DATESBETWEEN(Dates[FullDate],
      [Start Date],[End Date]))

We’re using the DATESBETWEEN function as a means of filtering our base [Sales] measure, and the [Start Date] and [End Date] measures that we defined previously are the end dates.

Note the use of the Dates table in the formula.  That is a third Date table, separate from the two slicer date tables, and that table IS related to the Sales table.

So…  we are picking up the user’s date selections from two tables that are NOT related to anything, and using the dates they selected to filter the “real” Dates table.  That filter then gets applied to the Sales table because it is related to the Sales table.

[Sales Off Program] is a bit trickier than “On” Program.  I suppose we COULD just subtract “On Program” sales from total [Sales], but just in case someday we need to build some date sensitivity into the base [Sales] measure, let’s defined [Sales Off Program] to be [Sales] filtered to dates OUTSIDE of the selected date range:

[Sales Off Program] = [Sales](FILTER(Dates,
   Dates[FullDate]<[Start Date] || 
   Dates[FullDate]>[End Date]))

First, notice that we are using the FILTER function this time rather than DATESBETWEEN.  There is no DATESNOTBETWEEN or DATESOUTSIDE function, so we have to express the logic more directly ourselves.  But that’s not a big deal – I’m pretty sure that DATESBETWEEN is really just a “skin” over the FILTER function anyway – I’ve used FILTER and DATESBETWEEN somewhat interchangeably over the past year and I always get the same results (and the same performance).

Also note the use of the “||” operator, also known as OR.  Rows from the Dates table are included if they are before the [Start Date] OR after the [End Date].

One more note:  you always want to use FILTER and DATESBETWEEN against the smallest tables you can.  Use them against your Dates table, for instance, rather than against the Date column in your Sales table, because they are MUCH slower against larger tables.

Here’s what we get with those new measures on our pivot, and with some Product hierarchy on rows:

image

Now you see why “per day” versions of these measures are required – the programs are so short that they are dwarfed by the “off” dates.

Note:  These two date slicers are great candidates for disabling cross-filtering and thereby improving the performance (update time) of this report.  See this blog post for an explanation.

Step Three:  Creating the Sales-Per-Day Measures

OK, first we need a [Day Count] measure that we can use as a denominator:

[Day Count] = COUNTROWS(DISTINCT(Sales[OrderDate]))

(Note that I am explicitly counting the Date column from the Sales table rather than the Dates table to account for product lines that did not exist for the duration of the entire Dates table, and therefore did not sell at all – there are tradeoffs here that I won’t go into, but I think this is the right thing to do in most cases when calculating a per-day measure).

[Sales per Day], then, is straightforward:

[Sales per Day] = [Sales] / [DayCount]

Now I need to create “On Program” and “Off Program” versions of that measure.  These formulas parallel the On/Off Sales measures from above:

[Sales per Day on Program] = [Sales per Day]    (DATESBETWEEN(Dates[FullDate],
    [Start Date],[End Date]))

[Sales per Day Off Program] = [Sales per Day]    (FILTER(Dates,
    Dates[FullDate]<[Start Date] || 
    Dates[FullDate]>[End Date]))

And lastly, let’s add the [Program Delta] measure, which is really Pct Change in Sales Per Day:

[Program Delta] = IF([Sales per Day on Program]=0,BLANK(),
   ([Sales per Day On Program] – [Sales per Day Off Program])/
   [Sales per Day off Program])

The IF in there is just to catch the case where a product did not sell during the selected dates.  If I leave that IF out, the measure will return –100% for those cases.  Sometimes that is what you want and sometimes it is not – it’s a case by case judgment.

Add some conditional formatting, and here’s the resulting report, repeated again from above:

image

Alternate Approach:  Using a Promotions Table

Rather than use two date slicers like we have here, you COULD have a single Promotions table that lists each promotion and its Start and End date.  Something like this:

image

Now, I just have to go back to my [Start Date] and [End Date] measures and change their definitions to reference the Start and End columns in this table, and “attach” them to this table instead of those slicer tables:

image

image

I don’t have to make ANY other changes.  Now I can remove the Start and End date slicers, and replace them with a single Promo Name slicer:

  image

Or, move Promo Name to Rows and some of the product stuff to slicers:

image

Other Fun Stuff

There are many, MANY other things you can do here too.  For instance, a measure that calculates sales per day for the month leading up to a promotion starting.  Or the month after it ends.  Or the exact same period 1 year ago.  PowerPivot truly does open doors that you’d never consider in traditional Excel (and probably would never get to in traditional BI).

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 15 Comments
  1. There’s a light at the end of the tunnel! I’ve been banging my head around trying to do some custom reports. I have various customers that I want to query (one at a time) but each customer cycle had different dates. I was using simple measure DatesBetween, but for each query I wanted to run, I had to physically edit the start/end date. Now I can assign a slicer – so … “I’ve got that going for me..”.
    Question to the next level. How do I compare period over period sales? I could have -2 years, -1 year, current period or by quarter or by month. I’ve tried several measures but I keep getting a Boolean logic error.

    1. Sounds like a future blog post actually, but here’s a hint to get you started John:

      Take your [Start Date] and [End Date] measures. Define new measures based off of those using date arithmetic.

      For instance:

      [Month Prior to Start] = [Start Date] – 31

      Now you can define new flavors of your core measures like “Sales for Month Prior” that use these new date measures as their endpoints.

      That may be too simple for many applications, but like I said, it’s a hint 🙂

  2. Great stuff, Rob! Good timing too; a few weeks ago I created something similar (read that as not anything as nice, useful and pretty as yours, not similar at all really) using date ranges and rewrote it after reading your blog post. It helped a bunch. I was using two IF(COUNTROWS(VALUES(…))=1 tests inside my date range measure to validate having one date selection per slicer and my measure was an overly complicated combination of CALCULATE, FILTER and VALUES. Your tip to use DATESBETWEEN and put the slicer values in their own measures with LASTDATE made for a very clean alternative. Thanks for sharing and please keep the tips coming.

  3. hi
    this formula:
    [Sales](DATESBETWEEN(Dates[FullDate],
    [Start Date],[End Date]))
    does not
    valid (maybe calculate missing?)

    what to put after [sales]?

    Thanks

    1. Where are you seeing that it is not valid? Having a measure name followed by open paren is “shorthand” for CALCULATE:

      CALCULATE([Measure], filters)

      and

      [Measure](filters)

      are 100% equivalent.

      1. Thanks a lot
        Its Work!
        one more question please:
        How can i solve this if i have accumulate sales for each month in the database?

  4. This is all really great stuff. Thanks for sharing.

    Any chance you have suggestions on how one may best tackle using a slicer to identify a start date and calculate total purchases made in time periods both before and after the date selected?

    For example: you want to see if a customer that purchased a mountain bike on selected date also made additional purchases both before or after the date selected and how many days from the dynamic date selected.

    Thanks!

  5. Hello, I am new trying to learn how power pibot works!

    I want to download the data so I can follow the example. Where can I find the data?
    (I also purchased the book though the data that comes there does not match this example)

    Thanks for everything

  6. Hey all,
    Thanks for this insightful blogpost. I seem to have one issue however.
    In a seperate PivotTable, the LastDate function works wonders – however, when I want to use it in a formula, it returns blanks.

    What I’m trying to do is filtering a list of “end dates” based on a user selected “end date” as described above (or specifically, select only those “end dates” that are later than what the user selects).
    So I have created the following measure: Filtered End Date:=CALCULATE([Updated End Date];FILTER(ALL(Updated_Input[Combined End Dates]);Updated_Input[Combined End Dates]>[End Date Slicer]))
    The thing is, when I hardcode the [End Date Slicer] as Year(2018;2;1) it returns what I want to have, but if I refer to it as the created [End Date Slicer], it returns blanks.

    The [End Date Slicer] is coded as: End Date Slicer:=LASTDATE(End_Dates[Project End Dates]), and in a seperate Pivot on [End Date Slicer], I can see that it shows the dates that I select in the slicer. I have also set all formats to “Date” (similar formats for all values), but I’m out of ideas.

    Would anyone of you know what the issue is here?

Leave a Comment or Question