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




About this tutorial:

Video duration: 22:45
Download Excel Finished Files:
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)…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

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!

    sadyaz64

    (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)

    Very helpful, thanks.

    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

Leave a Reply

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