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

Greetings from Israel!  I’m over here meeting with Microsoft and my old friend Dany Hoter Smile

But the blog marches on!

Here’s a topic that comes up about once a month, and it’s time that I tackled it.

What was the top sales date in a period?

OK, we’ve seen how a function like MAXX can return the maximum sales amount:

MAXX Measure Returning Top-Selling Single Day Amount

[Sales Units] =

[Sales Units on Max Day]=
MAXX(VALUES(Calendar[Date]), [Sales Units])

Since I have covered the “X” functions before, I won’t go into detail on how that second measure works – you can find an explanation here in one of my favorite all-time posts.

But in short, since the MAXX is iterating over Calendar[Date], it is returning the amount of [Sales Units] on the “top” day.  For instance, the most we ever sold of “All Purpose Bike Stand” on a single day was 6 units.

OK, But WHEN Did That Happen?

It’s just teasing us, sitting there, telling us that 6 was the amount sold on the best day.  But WHEN was that?  There’s four years of data here!  Was it recent?  Was it a long time ago?  In the summer?  Winter?  Tell me!

Let’s add a new measure:

[Date of Max Unit Sales]=
      [Sales Units]=
      CALCULATE([Sales Units on Max Day],

Essentially, what that formula does is scan through all the days in the Calendar table and find the one where the normal [Sales Units] measure matches the [Sales Units on Max Day] measure – if N is the max we ever sold, and I find a day on which we sold N, that day has to at least be “tied” for the best day ever!  (And in this case, we break ties with LASTDATE, which will pick the most recent of the tied dates).

The MAXX Measure Found the Top Item (Date) But Didn't Tell Me What that Item (Date) Was.  Now We Find It!

So 12/19/2003 is the best-selling day for the bike stand, or at least, is tied for the best day (in which case it is the most recent of the ties).

Dealing With Blanks

Actually, my measure has one more step in it:

=IF(ISBLANK([Sales Units on Max Day]),BLANK(),
         [Sales Units]=
         CALCULATE([Sales Units on Max Day],

Without that IF, I get rows in my pivot for products that never sold anything:


So it’s good to have the IF Smile

Why Didn’t I Just Test [Sales Units] = [Sales Units on Max Day]?

In the FILTER() you might notice that I test [Sales Units] against a CALCULATE expression, and not just the “raw” [Sales Units on Max Day] measure:

   [Sales Units]=
   CALCULATE([Sales Units on Max Day],

Why didn’t I just test [Sales Units] against [Sales Units on Max Day]?

Because, in the FILTER, the “test expression” (X = Y) is evaluated one Calendar row at a time, as if that is the ONLY calendar row in the universe.  So [Sales Units on Max Day] and [Sales Units] will always return the same value – the number of units sold that day.

CALCULATE, with the VALUES function, fixes that, and forces [Sales Units on Max Day] to be evaluated in the original context of the pivot, and not against that single Calendar row.  Since [Sales Units] is tested “raw” (not inside a CALCULATE), it is still evaluated for the single Calendar row, and we’re good.


And yes, you can slice this pivot by Year and the measure will return the best-selling date from the selected year.

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 12 Comments
  1. To return the Nth best sales total, use the following type of formula:


  2. this calculation should give you the same result without the expensive FILTER-function:
    Top Sales Day:=IF(NOT(ISBLANK([Sales Units])),
    “Max SU”,
    [Sales Units]),
    [Max SU]),

    using SUMMARIZE to turn filter-context into row-context and group it by day
    then use TOPN to return only the first row with the most [Sales Units]
    use MAXX() or any other aggregate function to convert the table returned by TOPN into a value – in this case we return the value of our Date-column

    what do you think about it?


    1. Hi Gerhard! I haven’t used SUMMARIZE at all yet – since my servers are still PowerPivot V1, I’m largely using V1 on my desktop too. And SUMMARIZE is new in V2. So I will let someone else comment for now, but I will link to your comment from the post I am about to publish 🙂

    2. Gerhard, I know this article is dated, but I wanted to say thanks for your summary example to calculate this. It is at least a 1000 times faster than using Filter(). Now I want to go through all of my measures and use summary functions!

  3. Works like a charm and very fast. Thanks Gerhard. I used Concatenatex instead of MAXX as I had to find the best selling color….

  4. Hello Gerhard,

    Excellent article and blog. How would I extend this to grab the top time? i.e. I had 1200 transactions on 3/20/2017 at 11:15am? My transactions table captures 15min intervals and is joined to a dim15MinGoup table.

  5. Sorry meant Rob!

    Great post and blog!

    I am also trying to get the timestamp of the max rolling total on any given day. Day is working fine, but haven’t been able to get the timestamp. Timestamp is a separate column in the facttable.


  6. HI,Rob

    =IF(ISBLANK([Sales Units on Max Day]),BLANK(),
    [Sales Units]=
    CALCULATE([Sales Units on Max Day],

    CALCULATE (FIRSTDATE (Calendar [Date]), …., I think it should return the first day of the corresponding date, such as 2003-12-1.

    But your pivot table, ALL-purpose bike stand results returned is 2003-12-19, what’s going on? Can you explain it?

  7. Instead of top selling date, I want to do Top selling Year. What would be the equivalent of FIRSTDATE to modify the DAX? I guess in other places I need to change Calendar[Date] to Calendar[Year]

    Thanks in advance

  8. Hello, I purchase product monthly from various vendor and I need help getting the last date and corresponding quantity received on my purchase order. I was thinking I could use the Lastdate formula but I also need the quantity. Any help would be appreciated.



  9. How can i alter this when i use it to get max month of highest sales in last 6 months.
    I need to get highest sale amount in last 6 months and corresponding month but not selected month. Any help would be appreciated. Thanks in advance

Leave a Reply

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