Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula: Excel Magic Trick 1552
HTML-код
- Опубликовано: 5 фев 2025
- Download Excel Finished Files: excelisfun.net...
Entire page with all Excel Files for All Videos: excelisfun.net...
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) Excel Worksheet Formulas, including AGGREGATE and AVERAGEIFS functions.
4. (05:27) LARGE Function and New Excel Office 365 Calculation Engine
5. (08:15) Power Query M Code, including Table.Sort, List.Average and List.FirstN M Code functions
6. (12:46) Power Query Parameter Query with condition coming from Excel Worksheet
7. (14:05) Power Pivot & Power BI DAX Measure, including TOPN, CALCULATE and AVERAGE DAX Functions
8. (17:08) DAX Parameter from Excel Worksheet using VALUES DAX Function.
9. (18:43) Add new records to table and test formulas
10. (19:00) Fix Power Query Table.Sort bug using Dummy Insert Column
11. (20:08) Fix Power Query Table.Sort bug using Table.Sort function inside Table.Group Function
12. (22:12) Conclusion
![Seungmin "그렇게, 천천히, 우리(As we are)" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/kAzmhLHePqU/mqdefault.jpg)
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... :-)
Mike, here’s a little request: can you do an exercise where you compute a cumulative column in DAX?
You get to use the following ingredients: index column, rankx, and topN. That should do the trick...
Furthermore, I wonder how to do that in PQ... Do you accept the challenge?
As you know: performing calculations across the rows/records is quite the challenge in PQ and DAX...
The look on their faces say: "Geert is awesome!!!!" : )
BTW, I do not know what you mean when you wrote: "import the variable/parameter into the right m-step without requiring an additional query"... What is the process?, what video did I show it in...?
@@GeertDelmulle , I have so many demands right now bearing down on me. I have seen both Power Query and DAX solutions... I thought I did a Power Query type running total... But now I do not remember. I just added your cool Running Total in DAX and Power Query to my list on my desk : ) But not sure when I will get to it...
@@GeertDelmulle , O Geert, you did it to me again... You ask a cool question and I can not resist... Below is the data set, Worksheet Formulas, DAX Formulas and DAX Formulas for running totals by Month in a column:
Data Set:
Date Units
5/29/19 36
5/30/19 64
5/31/19 42
6/1/19 76
6/2/19 77
6/3/19 80
6/4/19 12
6/5/19 39
6/6/19 75
6/7/19 82
6/8/19 59
6/9/19 56
6/10/19 37
6/11/19 55
[1] Worksheet Formula Cumulative Totals: =SUM(C$5:C5)
[2] Worksheet Formula Cumulative Totals by Month: =SUMPRODUCT(--(MONTH(B$5:B5)=MONTH(B5)),C$5:C5)
[3] DAX Calculated Column Cumulative Totals:
=CALCULATE(SUM(fUnits[Units]),FILTER(fUnits,fUnits[Date]
Mike, about: inserting a variable/parameter w/o additional query:
The process is this:
- create a one-cell table in Excel and load it into PQ - let’s call this the parameter query;
- in that new query drill down to the scalar value of the parameter;
- copy the row and column refs and paste them in the first (=source) step of said query;
- delete all other steps in that query;
- copy the formula from the source step;
- paste it to where-ever you need it (another step in another query) - e.g. as the value in a filter step, to make the latter variable.
- finally: delete the now redundant parameter query.
Done.
BTW: it was in that video where you avoided a Firewall Error Message in exactly this way!...
PS: it is quicker to do it than to describe it here in the comments. LOL! :-)
Love the different solutions. Thank you Mike!
You are welcome, Teammate!!! Just too many ways to have fun in Excel : )
Excellent video Mike! Love formula vs M code vs DAX comparisons. Please make more videos like these. My mind is blown.
Many more to come! Thanks for your support with your comments, Thumbs Up and Sub : )
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
You are welcome for the share and three way fun with Power Query Worksheet Formulas and DAX!!!
So much good stuff covered there, and good work flagging the insert index sort bug fix
So much good stuff and fun = that is because Excel is still the tool that offers it all: Worksheet, Power Query, Data Model, DAX!!! All except those delicious interactive visualizations and monthly updates like in Power BI : ) Glad you had fun with this good stuff, Teammate Wyn!!! : )
Thank you for the great video Mike, it is always great pleasure to watch, so informative. Have a great day Mike
You are welcome, Katerina!!!! Thanks for the good day wishes : )
Nice comparison and good to know how to fix the bug
Yes indeed, comparisons and fixes are fun and important : )
Thanks a lot. This video is very useful. 👍
You are welcome a lot! Thanks for your support!
love to see different solutions from formulas, Power Query, and Power Pivot!
Thanks, Excel Bear : )
Thanks for the awsome video Mike ! Great to see the different PT´s in action ! Thanks and as always thumbs up !!!!
Thanks for the always thumbs ups, Victor! What do you mean "PT's"?
Thank You Mike. Thumbs Up :)
You are welcome, nimrodzik1!!!!!
Awesome video. Helped me a lot as always. I would be stuck forever in that Query bug.
Good one mike... Its been a whileeeee since i have used aggregate function... Its all power query and dax now....
This video revised old memories... And i love it... Thanks for another awesome video
You are welcome! These are fun when we do it with Worksheet, DAX and M Code Formulas! What is it that you are doing where you are using Power Query and DAX all the time?
@@excelisfun i am automating most of the redundant tasks with power query and then summarising it using dax... And it saves tonnes of time... And using saved time it to have fun with R language
@@rrrprogram8667 RRRRRRRRRRR is fun : )
@@excelisfun lol...
Thank you ... I exactly stuck in that Power query bug.. and you enlighten me.
Thank you so much
You are welcome! I am glad that this video came out now to help with your current Table.Sort issue. Thanks for your support with your comment, Thumbs Up and your Sub : )
Great video showcasing the different solutions.
Thanks Mike for this EXCELlent video.
You are welcome for the EXCELlent video, Syed : )
This was great Mike, thanks!
You are welcome most awesome Excel Online Teammate Chris!!!!
How did I miss this great video!
I do not know? But I am glad that you found it. It definitely is an action packed video with lots of advanced tips : )
One problem, three ways to solve. Great idea! complete explanation. thank you Mike.
Glad you like it, enrique!
This was awesome video, thanks for sharing 👍
You are welcome for the share! Thanks for your support : )
Beautiful staff!!! Thanks mike.
You are welcome, John B. : )
Thanks Mike!
You are welcome, Dave!!!!
Which method do you like?
@@excelisfun I've had almost the same calculation to work on this week, I've had to use a sheet formula, but I like the DAX formulas, probably due to writing so many Access queries
@@davebowman5392 Cool : )
Love the theme music
Nice to have music while doing Excel : )
Amazing, thanks Mike.
Glad it is amazing for you, Luciano!!!
Perfect
Glad it is perfect for you!
Thumbs up, of course! Agregate, PQ PP - brilliant!
Glad you liked the video, Malina : )
Very helpful, thanks.
You are welcome for the helpful fun, ali!!
Your just... A GENIUS!!!!
thank you so so much I swear was looking for this formula
You are welcome, Ismail!!!
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}}))
Yes, great idea! Thanks, sherry : )
... or simply insert a step:
Buffer = Table.Buffer(#"Sorted Rows")
(...which may or may not be easier than prefixing the sort formula with Table.Buffer like sherry did...)
@@HachiAdachi Yes, insert step Table.Buffer is a great way to do it too! Thanks for being part of the Team : )
Great vidéo thanks
You are welcome for the video, sadyaz64!!!!!
Grandmaster Mike! You have helped me become a superhero. Has the Table.Sort been fixed in PQ yet?
Not that I know of. But I have not paid too much attention that this specific issue.
I am a huge fannn of DAXxxxxx
Me too!!!!
@@excelisfun but i guess only 2% of excel users must be using dax... This is bcoz of lot of factors...
I guess i have to initiate free classes to promote dax in india.. Whats ur advice??
@@rrrprogram8667 That is a great idea! Post videos at RUclips and tell all your co-workers : )
@@excelisfun Woww... Your videos make it look so easy to make videos on RUclips... But i understand the hardwork behind it...
I would rather use ur videos and promote dax.. :D
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!
Yes, large with zeros will work, unless zero in the biggest. It is so great that you are experimenting, learn tons and having fun!!! Me too : )
This I need 😊
Great, Qasr!!!! Which method do you think you will use?
Superb video
Glad you like it!!!
My mom says you guys rock!
I hope the videos do rock for you!!!
Amazing !!!!
excellent work
Glad you like it, Steve Nye!!!
Veryyy niceeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee tricksssss thanks for all Mikeeee
You are welcome, Mohamed!!!
Hi there,
Could you teach us how to get last value (text) in every single date or others dimension in dax?
Different solutions are cool
Yes, different helps to teach : )
خیلی عالی
Hi again, I've been trying the p query without grouping, using Table Selectrows, ListMin, ListMaxN,
your inside, outside trick, no good so far, ho hum.
Bill syzs alwasy has ultra fun with power query
That is soooo true! We love Bill Szysz!!!!
@@excelisfun wooww... Made ultra sin in spelling his name correctly... Szysz it is.. Haha
Mike... Have u used one note??.. What is ur take on it... Is it helpful??
No...
Благодарю!
: )
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?
What is your rule if there are duplicates? If a dup is included, then it is not the last three, right? Also, I do not understand what you mean when you write: "if data are more detailed".
@@excelisfun i mean if we are using fact table then you have records with the same product duplicated
Mike.. In a calculated column... Why previousmonth() DOESNOT work.... But month() works... How do we find which function are for calculated coulmsn??
The only way i can tell which functions work is to: 1) read Microsoft's incomplete and sometimes horrible help, 2) Test and see, 3) think about Filter Context and Row Context and how they interact... As we all know, DAX is not as polite as Worksheet Formulas where we can use the F9 key to see any part of the formula... : (
o_O So does this mean that anything using Table.Sort immediately followed by Table.Group is potentially incorrect after adding new data? :'(
I do not think so. Only some calculations. I have definitely has sorting, followed by other Power Query Steps where things worked correctly. I have not sure what the trigger is for error, that is what makes it so frustrating... Hopefully Microsoft will fix it.
@@excelisfun Thank you! :)
Before watching DAX, I had a go and used a hurdle so;
VAR hurdle = MINX( TOPN(3,VALUES(fSales[DateTime]),fSales[DateTime],DESC) ,fSales[DateTime])
VAR anser = CALCULATE([AvgM], FILTER(ALL(fSales[DateTime]),fSales[DateTime] >= hurdle ) )
RETURN anser
Can i send you my excel file to help me to summarize
Video games