Greetings from Israel!  I’m over here meeting with Microsoft and my old friend Dany Hoter 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: [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). 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: So it’s good to have the IF ### 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.

0 Shares