# Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula: Excel Magic Trick 1552

Video duration: 22:45
Entire page with all Excel Files for All Videos:

In this video learn how to Average a customers last three sales (last three dates) using Excel Worksheet Formulas, Power Query M Code and a Power Pivot & Power BI DAX Measure. Learn about the Table.Sort Power Query M Code Function and how there is a bug that prevents the next step from updating when source data changes.
Topics:
1. (00:07) Introduction
2. (00:50) Sort Method
3. (02:09)…

## 33 thoughts on “Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula: Excel Magic Trick 1552”

#### Geert Delmulle

(June 13, 2019 - 10:16 am)

Great video, subject and format — and action packed as always.
For me these comparison videos take the cake: one specific example and 3 ways to do it: just great!
Only very few people do this and none are as popular as you, Mike! Cudos!
BTW: I’d prefer it when you import the variable/parameter into the right m-step without requiring an additional query, you know, like you’ve done before. I actually literally showed that to some colleagues at work today (specifically: the keyword trick I mentioned earlier). Now, they have seen me use and explain PQ before, but they were flabbergasted when I constructed and “stole” the right formula and pasted it in the right place. Furthermore, coup de theatre: their jaws dropped on the floor when I removed the redundant query (I just added) altogether and it still worked… Hahaha, that was funny! You should have seen the look on their faces… 🙂

#### Ismail Ismaili

(June 13, 2019 - 10:16 am)

thank you so so much I swear was looking for this formula

#### Wayne Edmondson

(June 13, 2019 - 10:16 am)

Hi Mike.. more great stuff. On the formula side, with AGGREGATE, you used 6 (ignore errors) and did a divide on DateTime and the Customer Filter. I experimented and you could also use 4 (ignore nothing) and multiply, as in: =AGGREGATE(14,4,fSales[DateTime]*(fSales[Customer]=F5),3) which in essence is the same as how you constructed the LARGE solution, but without the need for CSE since AGGREGATE can handle it. This is correct.. yes? As a student, I'm trying to understand all the possibilities.. so tinkering for multiple solutions. The PQ and DAX methods are great too. Lots to learn at every turn. Thumbs up!

#### Wyn Hopkins

(June 13, 2019 - 10:16 am)

So much good stuff covered there, and good work flagging the insert index sort bug fix

#### Jamie Rogers

(June 13, 2019 - 10:16 am)

o_O So does this mean that anything using Table.Sort immediately followed by Table.Group is potentially incorrect after adding new data? :'(

#### Steven Nye

(June 13, 2019 - 10:16 am)

excellent work

#### RRR program

(June 13, 2019 - 10:16 am)

Mike.. In a calculated column… Why previousmonth() DOESNOT work…. But month() works… How do we find which function are for calculated coulmsn??

#### enrique dominguez

(June 13, 2019 - 10:16 am)

One problem, three ways to solve. Great idea! complete explanation. thank you Mike.

#### alwaysforreal

(June 13, 2019 - 10:16 am)

Excellent video Mike! Love formula vs M code vs DAX comparisons. Please make more videos like these. My mind is blown.

#### Leila Gharani

(June 13, 2019 - 10:16 am)

Love the different solutions. Thank you Mike!

#### Vida

(June 13, 2019 - 10:16 am)

Nice comparison and good to know how to fix the bug

#### Luciano

(June 13, 2019 - 10:16 am)

Amazing, thanks Mike.

#### Katerina Milosovska

(June 13, 2019 - 10:16 am)

Thank you for the great video Mike, it is always great pleasure to watch, so informative. Have a great day Mike

#### Haider Ali

(June 13, 2019 - 10:16 am)

Amazing. Power Query is magic. thanks for sharing all three ways in a single video. Love your new videos to handle a single problem with three different solutions. GBU.
Thanks for sharing

#### Денис Малев

(June 13, 2019 - 10:16 am)

Благодарю!

#### Malina C.

(June 13, 2019 - 10:16 am)

Thumbs up, of course! Agregate, PQ PP – brilliant!

#### Anna Bańcyr

(June 13, 2019 - 10:16 am)

Hello, Formula in Dax works only if there are no repetition of dates (i mean the date for the name is not repetitive /is grouped) what must be done if data are more detailed?

#### Chris Mancinelli

(June 13, 2019 - 10:16 am)

This was great Mike, thanks!

(June 13, 2019 - 10:16 am)

Great vidéo thanks

#### nimrodzik1

(June 13, 2019 - 10:16 am)

Thank You Mike. Thumbs Up 🙂

#### ce917

(June 13, 2019 - 10:16 am)

Mike is a legend!
There is no better place to learn excel other than Mike’s YouTube channel

#### Victor Friesen

(June 13, 2019 - 10:16 am)

Thanks for the awsome video Mike ! Great to see the different PT´s in action ! Thanks and as always thumbs up !!!!

#### ali mogharrebi

(June 13, 2019 - 10:16 am)

#### Syed Muzammil Mahasan Shahi

(June 13, 2019 - 10:16 am)

Thanks Mike for this EXCELlent video.

#### John Borg

(June 13, 2019 - 10:16 am)

Beautiful staff!!! Thanks mike.

#### sherry izzie

(June 13, 2019 - 10:16 am)

Hi Mike, Great tips! , Another way to workaround the table sort bugs is to buffer the sorting table: = Table.Buffer(Table.Sort(#"Changed Type",{{"Customer", Order.Ascending}, {"DateTime", Order.Descending}}))

#### Excel Bear

(June 13, 2019 - 10:16 am)

love to see different solutions from formulas, Power Query, and Power Pivot!

#### Kelly Ray

(June 13, 2019 - 10:16 am)

Love the theme music

#### Arun Thandapani

(June 13, 2019 - 10:16 am)

This was awesome video, thanks for sharing 👍

#### RRR program

(June 13, 2019 - 10:16 am)

Mike… Have u used one note??.. What is ur take on it… Is it helpful??

#### artcheezz37

(June 13, 2019 - 10:16 am)

Thanks a lot. This video is very useful. 👍

#### Sanju

(June 13, 2019 - 10:16 am)

Thank you … I exactly stuck in that Power query bug.. and you enlighten me.
Thank you so much

#### RRR program

(June 13, 2019 - 10:16 am)

Bill syzs alwasy has ultra fun with power query