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] =
SUM(Sales[OrderQuantity])

[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]=
CALCULATE(FIRSTDATE(Calendar[Date]),
   FILTER(VALUES(Calendar[Date]),
      [Sales Units]=
      CALCULATE([Sales Units on Max Day],
         VALUES(Calendar[Date])
      )
   )
)

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(),
   CALCULATE(FIRSTDATE(Calendar[Date]),
      FILTER(VALUES(Calendar[Date]),
         [Sales Units]=
         CALCULATE([Sales Units on Max Day],
         VALUES(Calendar[Date])
      )
   )
)

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

image

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:

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

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.

Sliceable!

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

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 7 Comments

  1. To return the Nth best sales total, use the following type of formula:

    =MINX(TOPN(N,FACTSALES,FactSales[SalesQuantity]),FactSales[SalesQuantity])

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

    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?

    gerhard

    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.

    Thanks!
    Doug

Leave a Comment or Question