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

In a comment on Tuesday’s post, Cory asked the following question:

“I want to use the stores start date to determine if it should be included in the same store calculation or not. Y/Y SSS calculation regardless if there are sales for that store this year or last for the specific date range I’m looking at (typically by month for last 13 months). The other twist to my SSS calculation is the store must be open for at least 15 months before it is included in my SSS Total. I hope my question makes sense. Thanks for any guidance you or anyone can offer.”

Remember that in my first post, if a store had sales last year, and this year, I included it in the SSS (Same Store Sales) calc.

If we have columns in our data indicating the date a store opened (and when it closed), it’s a very rational thing to want to use that instead.

So I invented some new columns on my fictional Stores table:

I'm Going to Use These (Fictional) Store Open and Close Dates to Drive my PowerPivot Same Store Sales Calc

I’m Going to Use These (Fictional) Store Open and Close Dates
to Drive my PowerPivot Same Store Sales Calc

So, how do we do that?

First, I create a measure to calculate when a store opened.  I add this to the Stores table itself:


The formula is quite straightforward:

[Open Date Measure] =

Note:  I could have used FIRSTDATE() just as easily as LASTDATE(), since this measure only makes sense in the context of a single store.  And in that case, the two functions will return the same date, since we’ll only have one row of the Stores table in that context.

This does what I need it to do when I put StoreID on rows:


Then I create a measure that calculates “how many days has a store been open?”

[Store Age at Period Start] =
INT(FIRSTDATE(Calendar[Date])-[Open Date Measure])

(I wrapped that INT function around it because Excel kept trying to format the result as a date rather than an integer).

For this to make sense, I need to have something from the Calendar table on the pivot, so for now, here’s Year-Month on a slicer:


So in January of 2008 (2008-01 on the slicer), store #1 had been open 2376 days, and store #9 had an age of –154, meaning that it was still 154 days away from opening.

Now, to determine if the store was open last year at this time, I add another measure:

[Was Store Open Last Year] =
IF([Store Age at Period Start]>365,1,0)


The Two Highlighted Zeroes Indicate Those Stores
Were NOT Open a Year Prior to August 2008

Yes, in that measure it might be “safer” to use 366 or 367 rather than 365 to account for leap years and those rare “off by one” problems, but you get the idea.

And in Cory’s case, where he requires that a store be open for a full 15 months, and not merely open 12 months ago, he can change that number to be 365+92 or something similar.

Store Close Date

Now I add two more measures to deal with Close Date:

[Close Date Measure] =

[Is Store Closed] =
IF(LASTDATE(Calendar[Date])>=[Close Date Measure],1,0)


In July 2009, Three of Our Fictional Stores Had Closed

Tying it All Together

Now we can calculate “sales that count” for this year and last year, just like we did in the last post, but this time we use a different set of tests in the FILTER function:

[Current Sales – Respect Open Close Dates] =
CALCULATE([Units Sold],
          [Was Store Open Last Year]=1 && [Is Store Closed]=0

[Same Store Sales Last Yr – Respect Open Close Dates] =
CALCULATE([Units Sold Last Yr],
          [Was Store Open Last Year]=1 && [Is Store Closed]=0

Only the highlighted portions differ from the original method used in Tuesday’s post.

Now it’s just a simple “(New – Old) / Old” measure:

[Same Store Sales vs Last Yr – Respect Open Close Dates] =

   [Current Sales – Respect Open Close Dates]-
   [Same Store Sales Last Yr – Respect Open Close Dates] ) /
[Same Store Sales Last Yr – Respect Open Close Dates]

And we test it out in a new pivot that just has Year-Month on rows:


Hey That’s a LONG Measure Name!

Yes.  Yes it is.  I prefer to give my measures VERY descriptive names so that 6 months from now I know what the measure actually DOES.  That makes the pivots unwieldy to look at though.

No worries.  Just select that measure header cell in the pivot and type over it:


The field list changes to reflect that, but only in one place:

PowerPivot Custom Measure Name Appears in Drop Zone but Not in Measure List – Genius!

Custom Measure Name Appears in Drop Zone but Not in Measure List – Genius!

I LOVE THIS.  I can quickly tell “what’s what” this way.  I could write pages on how much I like this, but let’s skip that and just leave it at “I really think this was a great design decision” (and no, *I* had nothing to do with it – this happened after I left MS).

Download the Workbook

Download the Updated Workbook Here

One Advantage of the “Did We Have Sales” Approach

As a parting comment, one of the hidden advantages of Tuesday’s method is this:  if a store was open last year, but it didn’t even have a department that sold fruit until last month, the Open/Close date method will tell us that the store should “count,” but really, it shouldn’t.

The “did we have sales” method, however, automatically accounts for this, on a per-product basis.  That’s pretty cool. 

Of course, if certain products don’t sell very often even when they ARE offered for sale, the “did we have sales” method can again be misleading – sometimes, not selling something means does NOT mean that you weren’t trying.

Every business situation is different, and it’s therefore good to have many different methodologies you can choose from.  There is no “one size fits all” so I’m happy to cover alternatives and contrast the tradeoffs.  Neither is “better” – you have to choose.

Stated differently, you GET to choose Smile

Rob Collie

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 11 Comments
  1. I was thinking a potential way to elect a fitting candidate for the ‘open store date’ might be to hybridize both methods…
    Start by considering the OpenDate, calculate the time lapse between OpenDate and FirstSaleDate (let’s call it FirstTimeLapse), then the mean time lapse between Sales, starting from FirstSaleDate. If FirstTimeLapse isn’t within a given tolerance threshold of MeanTimeLapse, perhaps it is more appropriate to select FirstSaleDate as the real open date (or a date calculated using FirstSaleDate, like first day of that month, etc.)?
    I’m not sure if such detail is needed, just some thoughts off the top of my head.
    Anyway, really liked those recent articles of yours!

    1. (Oops, originally posted the wrong comment here)

      What I meant to say was: yes, I think that is a very good fit for many business situations. I love this about PowerPivot – once we get started down a road, all kinds of options open up. And since the Excel Pros working on this stuff tend to be tightly embedded into the “business side of the house,” we are properly positioned to come up with precisely the right ideas.

  2. HI there,

    Glad to see a post dealing with SSS on retail business
    I’m working in that business as financial controler for a while, and since day one, I struggled to set up somthing automatic to calculation SSS.
    I managed through different excel formulas, until powerpivot has been created and released.
    During years, I was found of the “did we have sales” approach. Makes more sense to me. But as said, every business situation is different. Now some folks want to have a similar approach as described by Cory.
    The only difference with situation described by Cory are :
    – are included on SSS only shops that are opened 12 months without discontinuing on both 2 fiscal years (a fiscal year here starts in July 1st Y and finishes June 30th Y+1)
    – some stores are opened during two successive fiscal year, but are somewhen during a year, closed for refit. And so naturally they should be excluded from like for like comparison. So compared to the fictional open/close store table you’ve created here, one shop can have several opening and closing dates.

    To manage that, so far I created a list of stores that are to be included in SSS, and maintain that list month after month (depending on closing / opening). But I’m keen to automatise it a little bit more.

    If you have any idea, I’ll be very grateful, and pretty sure a lot of folks reading your blog will be happy to learn that.

    Have a nice day


    1. I haven’t tried this but I think I have an idea how to do what you are asking:

      1) Create a new table in the model called StoreActiveDates (or similar)
      2) This table has three columns – StoreID, StartDate, EndDate
      3) Unlike the Stores table though, this table can contain MULTIPLE rows for a single store
      4) Relate this new table to the Stores table, using the StoreID column

      Now write a measure (or measures) on the StoreActiveDates table that answers the question “Should this store count?” It might be a CALCULATE(COUNTROWS(), expression…) type of thing.

      Lastly, change the filter expression in the FILTER function of the measures covered here to use THAT measure rather than the [Is Store Closed] measure for instance.

      1. ok thanks
        I’ll try despite I hardly see what formula I’ll put in there
        I’ll keep you posted
        If in the meantime you have a trick/advice, be welcome to share it
        And once again : your blog rocks !!!!!

      2. How about something like:

        [Qualifying Store Count] =

        TheNewTable[StartDate] <= FIRSTDATE(Calendar[Date]) -365 && TheNewTable[EndDate] >= LASTDATE(Calendar[Date]

        and then in your FILTER for the SSS measures themselves, do something like:

        FILTER(Stores, [Qualifying Store Count] > 0)

  3. First off thanks for all the great real world examples and overall awesome content you’ve made available.

    I’m new to PowerPivot so forgive me for this basic question. I was just wondering why we need to create the measure for Open Date as the first step if it’s just going to pull the date directly from the existing Open Date column? Is there something special about creating a measure that allows you to use it in these various formulas where a direct reference to a column does not allow this?

    1. Hi Ben!

      PowerPivot measures work with aggregates. For instance, you can’t define a measure with a formula of =SalesTable[Amount Column] – you need to have an aggregation function or you will get an error. So SUM(SalesTable[Amount Column]) is legal.

      Strictly speaking I did not need to define an [OpenDate] measure. I could have just used LASTDATE(Stores[OpenDate]) everywhere in my other measures where I referenced [OpenDate]. But I needed an aggregation function (LASTDATE in this example) to make my formulas legal. At that point I might as well just define it once as a measure and re-use it.

  4. I hope i am not too late in this discussion but never the less.. This is a great article. I am fairly new to power pivot. I have been able to calculate the SSS using the above approach if i have stores in my label field but if i try to do the SSS analysis on city level (by putting city on row field), i guess it gives the wrong output. Can u pls help me how to correct this. I am trying to calculate the SSS on product,store,city level.

  5. How can you do same store sales by day exactly like SAMEPERIODLASTYEAR() but i need to offest the LY by one day . The reason being is to match up day of the week comp. Example 1/1/2015 (Thursday) comp against 1/2/2014 (Thursday)

  6. Hi Rob,
    Your blog is being of great help to me.
    I created the comparative Tuesday tuesday, it works OK.
    But what would MTD look like? If i use
    MTD_LY = calculate (MTD_Sales; sameperiodlastyear (DimaDate [Date]) does not display the same comparable period, what returns is accumulated since day 1 of the month, not the same period of days.
    I’m stuck.


Leave a Reply

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