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


Play Write the greatest song formula in the world, or I’ll eat your souls.”
-From “Tribute,” by Tenacious DAX

A Do-It-Yourself Time Machine

You remember the Great Precedence Project?  Well, I have shelved it for now, for two reasons:

  1. Creating an exhaustive list of all the precedence rules in PowerPivot formulas was going to be…  exhausting.
  2. I didn’t really need to boil the philosophical ocean in search of deep underlying meaning, because on further inspection, I already had the Greatest Formula in the World, and needed no other.

So what is the GFITW about?  It’s about time navigation.  It’s useful when you have a custom calendar and the time intelligence functions are therefore not so helpful.

The GFITW is a do-it-yourself time machine.

Year on Year Sales With a Custom Calendar?  Yes We Can!

In that post linked above, which happened to be the last installment of the precedence project, I was calculating a running total, and doing so without benefit of the fancier functions like DATESYTD etc. (which rely on the “real” calendar, not your business calendar).

OK, so now let’s do a “Year on Year” sales measure, sometimes called a “Year over Year” measure.  And again, our “calendar” table isn’t a true calendar at all:

Semantically this IS a calendar but functions like PREVIOUSYEAR can't use it

A Table of Periods (Not Dates) Means You Cannot Use the Built-In
Time Intelligence Functions to Calculate Year-on-Year Sales

And the Sales table is very simple:

Sales Table linked to a period number, not linked to a date

Sales Table is Linked to the Periods Table by the Period Num Column

And I have this basic pivot already set up:


Year and MerchPeriod on Rows

So, how do I write a measure that “fetches” the sales from last year?

Let’s Skip Some Steps

I am going to be honest with you:  it took me a very long time to figure this out.  The formula looks reasonably simple, and it is, but I had to try a million variations before I got it right.  Literally, it took me about a week to refine it.

So rather than walk you through that painful process, let’s skip ahead and show you the GFITW in all its glory.  Then in the next post I can show you what NOT to do, and we can also fine tune it.  OK?  OK.

So here it is, the Greatest Formula.  In the World:

=CALCULATE([Total Sales],

And its results:


No, it’s not perfect yet.  We will need to calculate percentage growth of course, rather than merely fetching last year’s sales.  And that given that 2011 is not yet complete, I’m a little squeamish about Last Year Sales returning ALL of 2010’s sales (the $5.9M number).

But for now, let’s count our blessings.  This formula DOES fetch the sales from last year, and it works (mostly) both at the year level and the MerchPeriod (aka Month) level.

Dissecting the Formula

Here is the formula again:

=CALCULATE([Total Sales],

But really, the GFITW is not a formula.  It is a pattern that you can re-use and modify.  So let’s look at it that way:

=CALCULATE(Original Measure,

   ALL(Your Custom Calendar Table),
   FILTER(ALL(Your Custom Calendar Table),
      Year Column Or Similar =
Expression that “moves” the Year Column
Time Column That is More Granular
          Than Year, Like Month or MerchPeriod



Where everything in blue is “fixed” as part of the pattern, and everything in italics is something you can change.

Like I said, this is running a bit long for one post.  Come back Thursday for the rest 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 13 Comments
  1. i’m not getting something.

    i have a table showing sales on a weekly basis for a variety of stores. i’m trying to add a column to the table, PreviousWeekSales, for the purpose of week over week comparisons by store.

    in the sales table, there are Store, WeekNumber (1, 2, 3, etc.), and SalesAmount columns.

    there are no other date or time columns involved.

    per your post, i created a separate WeeksTable, for all my WeekNumbers, and included a DayNumber column (1-7, repeating over each WeekNumber), since your formula-template indicated i need a finer grain than the weeks i’m actually interested in.

    my end result for the new column was this formula:

    = CALCULATE([m_SalesAmount],
    FILTER(ALL(WeeksTable), WeeksTable[WeekNumber] = MAX(WeeksTable[WeekNumber]) – 1),

    looking at the data by store-week, it simply shows the CURRENT store-week SalesAmount.

    What am I missing here?

    1. thanks for the reply.

      i initially tried it as a column, but i get the same result (current week is actually displayed) when i do it as a measure.


  2. Very useful post, thank u so much for doing this.

    One question from my side, i have similar task as sherifffruitfly, i have two years and 53 weeks in each year of data and sales amount similar to one in your example. I need to calculate average sales between current week and previous one. As a first step i need to find the value of sales of previous week but because i don’t have any “More Granular” values it all messes up. Can u advice how to solve it?
    Thank u so much in advance.

  3. This formula works great for our accounting prd/fisc yr based ytd total on journal_amt. However, whenever I try to extend it to have another filter, for say a journal amount class of expense only, to get the expense ytd total, It loses the running total feature. Can you help please? Thanks!!!

  4. Not only did this post end a stupid number of hours trying to bash a DAX formula into behaving, I had Tenacious D stuck in my head while conquering the beast! You rock!

  5. I’ve been using the GFTW at work for a while now, but when i set up sample workbook to play around in PowerBI, i couldn’t get it to work on month name (w my period# relationship). I like this version w VALUES. This whole formula is a good reminder to think a out context, then think what the filter is actually seeing at that point in the table. Doing so makes VALUES on month name totally clear. Thanks Rob!!

    1. Looking into more depth in the article of Kasper, I’m not sure it is the same? In the article he doesn’t use an ALL() function. I’ve tried it and compared the results (Replace ALL(table) by VALUES(column) and it seems to be the same. Unsure whether it is working correctly in all situations.

      1. Okay never mind my comments. I have tested the expressions a bit further and it seems that this is not going to work. Neglect my comments above.

        Anyway, my problem is that it takes about 3 seconds to use this GFITW pattern for about 260000 rows. I have all kinds of these DAX expressions in my PPV workbook and it takes about 6 or 8 seconds to respond to a slicer button. Deploying it to SharePoint makes it even worst (twice as slow). So desperately seeking for performance improvements.

  6. THANK YOU, THANK YOU. I have been fighting with something similar for the past 3 days (I want to pull a previous number) into the current month, and could not figure this out. Once again thanks for publishing this.

  7. Hello, First of all I apologize for my english. I am trying to use une of the formules/functions related to custom calendars I have read in the book.
    The function is
    Calculate ([Sales per Day in Period]; All (Periods); Filter (All (Periods); Periods [PeriodID]= Values (Periods [Period ID]) -1))

    My problem is with the Values function that provides a Table insted of a value. Can you help me whith de problem?.
    Thank you in advance

Leave a Reply

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