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


image

Our Time Machine From Part 1 Needs a Couple of Fixes

OK, if you recall from part one, we had a “year over year” time machine calculation going on, and it was built against a data set that lacked a “real” calendar table.  Very cool.  Very resourceful.  And very necessary.

But the time machine had two problems.  Let’s fix them.

Problem #1:  Meaningless Grand Total

Let’s start with the easy one:

Year over year in PowerPivot - Meaningless Grand Total

Grand Total is Meaningless for Last Year Sales

It is meaningless to have a “grand total” value for a measure that returns “last year’s sales.”  What year would that BE, actually?  It’s nonsense.  So we use an old trick, one of the many flavors of IF(VALUES()):

=IF(COUNTROWS(VALUES(Periods[Year]))>1,BLANK(),
CALCULATE([Total Sales],
ALL(Periods),
FILTER(ALL(Periods), Periods[Year]=MAX(Periods[Year])-1),
VALUES(Periods[MerchPeriod])
)
)

Where the new IF “wrapper” is highlighted and the original formula is in normal font.

For more on that “IF VALUES” technique, please see this post.

The results are as desired, the grand total is now blank:

image

Problem solved.  Moving on…

Problem #2:  2011 isn’t complete but we’re getting all of 2010 sales

Remember, our sales data for 2011 only goes through the first 6 months.  So we do NOT want “last year sales” for months 7-12:

image

That can be solved a number of ways.  The simplest is just to add another IF(), and have the Last Year Sales measure return BLANK() whenever there are no Sales:

=IF(COUNTROWS(VALUES(Periods[Year]))>1,BLANK(),
   IF([Total Sales] = BLANK(), BLANK(),
      CALCULATE([Total Sales],
ALL(Periods),
FILTER(ALL(Periods),
Periods[Year]=MAX(Periods[Year])-1
),
VALUES(Periods[MerchPeriod])
)
)
)

That trims the results to just the months desired:

image

But note that the subtotal for 2011 is still too high:

image

“Last Year Sales” for 2011 Should Only be Returning
2010 Sales Through the First Six Months

This one is trickier.  I struggled to find a good answer before coming up with the following:

Step 1:  Add a “Next Year Period Num” Column to the Periods Table

Recall that my Sales table has a “Period Num” column that is the basis for the relationship with the Periods table:

image

And that matches up with a similar column in the Periods table:

image

Note that Period Num does NOT reset to 1 with each new year (unlike MerchPeriod).  So that is the absolute unique ID for a given Month/Year combo.

Well, I created a new column in the Periods table that tells me, for a given Period Num, what the equivalent Period Num will be NEXT year:

image

Now, in my FILTER statements, I can choose to match on THAT column instead.

Bringing it Home

OK, now that I have that column, I can add a new FILTER clause to my measure:

=IF(COUNTROWS(VALUES(Periods[Year]))>1,BLANK(),
CALCULATE([Total Sales],
ALL(Periods),
FILTER(ALL(Periods),
Periods[Year]=MAX(Periods[Year])-1
),
      FILTER(ALL(Periods),
Periods[NextYear Period]<=
MAXX(
VALUES(PeriodSales[Period Num]),
PeriodSales[Period Num] )
),

VALUES(Periods[MerchPeriod])
)
)

OK, what does that do?  Simply put, it further filters the Periods table to NOT go beyond any periods for which we currently have records in the Sales table.

And the results:

Last Year Sales Measure in PowerPivot With a Custom Calendar

Last Year Sales Measure Doing the Right Thing

And now if you want YOY Growth, it’s straightforward:

=([Total Sales] – [LY Sales Finished]) / [Total Sales]

Which yields:

Year over Year / Year on Year Growth Percentage Measure in PowerPivot With a Custom Calendar

Year over Year / Year on Year Growth Percentage
Measure in PowerPivot With a Custom Calendar

OK, I cheated and wrapped another IF(COUNTROWS(VALUES)) around the outside of the formula so that the grand total cell is blank again, because again, that’s a meaningless value to report.  Details.

Isn’t that a lot of work?

Depends on how you look at it.  First of all, it’s a lot harder to grasp the first time than it is as you get used to it, trust me.

But even better, this investment pays off forever.  Normal Excel formulas may be easier to write the first time, but then you have to re-write and adjust them forever, every time your data changes.  And every time your desired report shape changes.

This one is a portable formula.  It goes wherever you want it to go, and eats whatever data you feed it.  Forever.  It’s worth a little extra effort.

OK but do I really have to understand all of that?

No, not really.  Not right away.  Think of it this way:  this is what I do all day, every day.  And I can tell you that figuring this out is not something you want to do in your spare time.

But once someone gives you the pattern?  Wash, rinse, repeat.  Make small adjustments as needed.

Honestly, that whole formula above could be wrapped up in a function provided by Microsoft, and all of the nasty stuff hidden.  I told them as much last time I visited Redmond.  And if it were a function rather than a formula, we wouldn’t care at all how it worked.

So… treat this as a pattern.  Copy/paste and modify to fit your needs.  Seriously.

Unfinished Business

I actually WILL retrace my steps here though and explain a number of things though, so that you CAN understand.  I have a couple of posts in the queue aimed at just that.

Were there alternate ways to write this measure?

Yes, there were, especially in that last step where I needed to get the “Last year sales” measure correct for the 2011 subtotal.  I could have used SUMX to make the year subtotal equal to the sum of its underlying months.  That is worthy of a post for sure.

But I was wondering if there was still another way.  A way that doesn’t use SUMX and doesn’t require a new calc column.

And for that, we need the Italians.  So I’m gonna try out my new toy, the Boot Signal:

image

Calling Marco and Alberto

Get it?  *Boot* Signal?

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. Rob – I’m emailing the measure because wordpress seems to be interpreting the text and doing something completely nuts with it. Sorry!

  2. The following is a (hopefully) a better solution.
    At least you don’t have to create a new calculated column (NextYear Period).
    However, it still has a SUMX, but I’m not sure we can figure out how to write a single CALCULATE-based formula that doesn’t use SUMX and works at any level of granularity.

    =IF (
    COUNTROWS (VALUES (Periods[Year])) = 1,
    SUMX (
    VALUES (Periods[MerchPeriod]),
    IF (CALCULATE (COUNTROWS (PeriodSales)) > 0,
    CALCULATE (
    [Total Sales],
    Periods[Year] = VALUES (Periods[Year]) – 1
    )
    )
    )
    )

  3. Hopefully you can help me with TGFITW, as I’m trying to adopt into one of my models.
    It calculates and shows perfectly, as long as I’m showing years/months/weeks/days in the rows. But I need it to calculate correctly when my rows shows salespersons/products or the like. For the moment it returns the full year values.

    1. So you are saying that, when you don’t have a date-related field on rows, the calcs return full-year values? That doesn’t surprise me much but it would be good to know a little more before I answer.

      Can you copy/paste your formula here?

      1. Here is my formula:
        =IF(COUNTROWS(VALUES(DateTable[FiscalYear]))>1,BLANK(),
        IF(CALCULATE(SUM(Invoiced[Invoiced Quantity Base]),DATESYTD(DateTable[DateKey],”30-06-2000″)) = BLANK(), BLANK(),
        CALCULATE(CALCULATE(SUM(Invoiced[Invoiced Quantity Base]),DATESYTD(DateTable[DateKey],”30-06-2000″)),
        ALL(DateTable),
        FILTER(ALL(DateTable),
        DateTable[FiscalYear]=MAX(DateTable[FiscalYear])-1
        ),
        FILTER(ALL(DateTable),
        DateTable[NextYear DateInt]<=
        MAXX(
        VALUES(DateTable[DateInt]),
        DateTable[DateInt]
        )
        ),
        VALUES(DateTable[FiscalMonthNo])
        )
        )
        )

        The DateTable is the BasicCalendarDanish-dataset (DateStream) from Azure-marketplace.
        The [NextYear DateInt], is the [DateInt]+10000.

        I would like it to go as detailed as date (not only month), which I guess is not my biggest problem. It works, when I use cube-formulas and add a date as parameter. Could I add todays-date as part of the formula above?

  4. I don’t know if you still frequent this page, but I am in a conundrum. I am trying to take 400+k rows of data, count them by MonthYear and perform a YOY check in a Pivot with splicers to select key months. I am EXTREMELY new to PowerPivot and sort of lost. If I was able to perform “Calculate a field” in a normal pivot, I could just do =SUM(new-old)/old*100 but PowerPivot just isn’t the same….

    1. Let me try to point you in a direction… hopefully, I’m understanding your question correctly!

      To try this, you can just enter this example/test data into an Excel sheet/tab. Then add the data to the PowerPivot data model as a Linked Table data source.

      1.) Your 400k data set, let’s call this 1st table [TRANSACTIONS] you should include a date field and then whatever values you’re trying to total or compare – for example, maybe you have [UNITS], [REVENUE] and [TRANSACTION_DATE]

      1a.) For example:

      100, $500, 01/01/2014
      150, $750, 01/02/2014
      125, $550, 12/31/2014
      200, $1000, 01/01/2015
      250, $1500, 01/02/2015
      225, $1100, 12/31/2015

      2.) You should have a 2nd table called [CALENDAR] with 1 unique record for each day in the [TRANSACTIONS] time frame. For each [CALENDAR_DATE] you would have [CALENDAR_MONTH], [CALENDAR_QUARTER] and [CALENDAR_YEAR]. as applicable.

      2a.) For example ([Calendar_Date], [Calendar_Month], [Calendar_Quarter], & [Calendar_Year]):

      20140101, 2014-Jan, 2014Q1, 2014
      20140102, 2014-Jan, 2014Q1, 2014
      20140103, 2014-Jan, 2014Q1, 2014…
      20141231, 2014-Dec, 2014Q4, 2014
      20150101, 2015-Jan, 2015Q1, 2015
      20150102, 2015-Jan, 2015Q1, 2015
      20150103, 2015-Jan, 2015Q1, 2015…
      20151231, 2015-Dec, 2015Q4, 2015

      3.) Relationships are always MANY to ONE in PowerPivot data model… therefore, you would Create Relationship and join [TRANSACTIONS] to [CALENDAR] by [TRANSACTIONS].[TRANSACTION_DATE] = [CALENDAR].[CALENDAR_DATE]

      4.) In order to use Time Intelligence, you will need to select the “Mark As Date Table” option in the PowerPivot data model window. You can find this option by selecting your [CALANDER] data table in the Power Pivot Window, then select the Design tab… it will ask you which field is the date & you can tell it to use your [CALENDAR_DATE] field.

      5.) Once you have the 2 tables in PP data model, Joined the 2 tables, and selected the [CALENDAR] table “Mark As Date Table”, then you can create a pivot table to get totals by the[CALENDAR_YEAR].

      6.) To do the calculations that you mention in your question of “SUM(new-old)/old*100″… I think you are meaning “Current Year” as (new) and “Previous Year” as (old)? If that’s the case, you can use Time Intelligence formulas to do this.

      In Excel, select the PowerPivot tab & Calculated Fields, enter a new Calculated Field to the [TRANSACTIONS] data table:

      6a.) First formula would be:
      [Current Year Revenue] =CALCULATE(SUM(Transactions[REVENUE]))

      6b.) Second formula would be:
      [Previous Year Revenue] =CALCULATE(SUM(Transactions[REVENUE]),PREVIOUSYEAR(Calendar[CALENDAR_DATE]))

      You can create a Pivot Table then to see these formula values… for this example, you would see the following:

      [CALENDAR YEAR], [Current Year Revenue], [Previous Year Revenue]
      2014, $1800
      2015, $3600, $1800

      7.) Then you can use the newly created Calculated Fields to do the final calculation
      [Year Revenue Change] =(([Current Year Revenue]-[Previous Year Revenue])/[Previous Year Revenue])*100

      If this doesn’t quite answer your question, hopefully, it’ll give you some ideas to lead you to the answer you need.

      Good Luck!

  5. Hi Rob, this is an awesome formula as I was having the same issues with fiscal calendars. Im trying to extend this to compare average price growth by replacing the [Total Sales] measure with “=sumx(Table,[Amount]/sum[Units])”. But the pivot is wrong at year 3 onwards. Its weird because year 2 reflects all the relevant year 1 comparisons.

    Can you please help?

    Last Year Average Price:
    =IF(COUNTROWS(VALUES(dDate[Financial Year]))>1,BLANK(),
    IF([Average Price]=BLANK(),BLANK(),
    CALCULATE([Average Price],
    ALL(dDate),
    FILTER(ALL(dDate),
    dDate[Financial Year]=MAX(dDate[Financial Year])-1),
    FILTER(ALL(dDate),
    dDate[Next Date]<=
    MAXX(
    VALUES(fTransactions[GL Date]),
    fTransactions[GL Date])),
    VALUES(dDate[Week])
    )
    )
    )

  6. Where do you get these tables from? Is there somewhere that I can download the 4-4-5 calendar with all of the columns already set up that you are discussing in these posts?

  7. I used the version from your book which didn’t use the VALUES section. Tables have the same structure as the book tables.

    The first prior period entry is always blank, all the others work ok.

    Any ideas?

    Sales per Day in Period = DIVIDE([Total Sales],[Days in Period],0)
    Days in Period = SUM(Period[Days in Month])

    Prior Period Sales per Day =
    IF ([Total Sales] = BLANK() ,
    BLANK(),
    CALCULATE(
    [Sales per Day in Period],
    FILTER(
    ALL(Period)
    ,Period[PeriodID] = MAX(Period[PeriodID])-1
    )
    )
    )

  8. Its OK the above does work – I got confused with the fiscal year and the calendar year – the only it doesn’t work is when there is no prior period – ALL GOOD!

Leave a Reply

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