Topics: 1. (00:07) Introduction 2. (01:20) Excel Worksheet Formula solution using SUMIFS and EOMONTH functions with an Expandable Range. Two formulas: Overall Running Total and Running Totals by Month. 3. (04:32) Look at expandable range in a Worksheet Formula 4. (05:18) Import Table for both Power Query Solution and DAX Solution using Power Query and the Reference feature. 5. (06:50) Power Query Custom Column with Table.AddColumn Function, Table.SelectRows Function and Two M Code Custom Functions to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column. 6. (08:15) Lesson about how Custom Columns and M Code Custom Functions work, including the how the keyword “each” is a substitute for an explicitly defined Custom Function. 7. (13:33) Look at expandable range in a Power Query Formula. 8. (14:25) Field Access Operator for Looking up: 1) A Full Column or 2) A Single Item from Each Row. 9. (20:01) DAX Calculated Column with the functions CALCULATE, SUM, FILTER, EARLIER, EOMONTH and a Var Variable to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column. 10. (26:46) How to use a Variable in DAX. See Var = Mon Return syntax for a Variable in DAX. 11. (30:26) Summary
I want to say thank you to you. After following your youtube videos for 1 year, I have learned so much and found a great job doing data analysis in a great company. thank you so much!
Very insightful video Mike, I really need to re-watch it to grasp its ideas. Actually, I've done it in an alternative way using PQ, but for running total restart by each store from transaction table as follows: 0- Create query from table then correct "Date" to type date. 1- Reference step 0 as new query 2- Sort by store & date 3- Add 0 index 4- Group by store for min of index 5- Merge #"Added Index" with above step based on store 6- Expand [Grouped Rows] to [min] 7- Resort by index (as expand disturb the order) 8- Add custom column =List.Sum( List.Range( #"Sorted Rows"[Amount], [min], [index] - [min] +1) Initially, I buffered step 1, despite that it was taking long time to refresh with +1000 record. But after seeing your video I buffered step 7 instead and it does drastically help. As beginner-to-intermediate user, it is not clear what actually Table.Buffer does and when & at what step to use it. Also is there a more efficient way to do vertical calculations (e.g running total) in PQ?
A thousand thanks, Mike! This is a beautiful treatment of iterative functions and power query. In all my reading and research, I found nothing that more clearly illustrates both the concepts and the practice. Many thanks again!
You are welcome, Jeff!!!! Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
Thank you for this, I personally found the DAX code easier to understand. This has solved a problem I've had that googling for days hadn't been able to fix.
Hi Mike, I have never thought to use table.selectrows and customer M code to do running total. I always add index starting from 1 then use List.sum and List.FirstN to do it. Learn something new today. Awesome!
15:30 love that nugget about the differences of each verses field access operator on an entire objects, in that case the derived column. Great video thanks Mike!
OMG was this ever good. I learned so much from this. You're such a great teacher....each minute that goes by I'm getting so much info... I'm literally "learning by the minute" watching your content. Keep it up! 🌝
Yes, sir! I am glad that you have found some other content that is useful to you. As I mentioned, I had a family tragedy and the MECS series is only progressing at a slow pace. It will be a full class - the best I have ever made - in the near future!!!
This is SO amazing!! Love it. I was able in Power Query to do a nested running total within another column just by adding " and IT[column]=OT[column]" directly after the
This is the best video I see about this topic! I had found another video with another custom function, but I think your solution is MUCH BETTER! And you explained all the way ( starting from Excel was a great teaching technique) ! I had two channels and you deserve 2 subscriptions! All the best from Brasil!
Awesome tutorial & perfect timing, Mike! I'm working on a fairly robust inventory status & production planning workbook that includes calculation of a projected date on which existing inventory will be exhausted based on consumption in accordance with the current production plan (which changes weekly or more frequently). Sort of a reverse running total :-) I knew how to do this in Excel with formulas, but wasn't sure if/how to do this in PQ (preferred) or DAX (if needed). I took a break for lunch & figured I'd hunt for some training on the topic. (My kids would tell me to take a real break, but - hey! - I don't pick their entertainment.) Of course, ExcelIsFun was my first choice from the search results. Spot on! I'm looking forward to building this technique into my models. Thanks!
I love to hear that this helped with your solution!!! Can you send me the workbook when you get the final solution, so AI can take a look and admire: excelisfun at gmail : )
Buffer and OT/IT comes again, finally this time I can understand how it works, while still far away from use this type of code in M code. And the VAR is new in DAX , interesting to know it can be used in such way , great video , keep learning ~
This was an epic video! Really cool series of tricks between Power Query and Power Pivot. It was an extremely clear explanation of iterating functions and generating a specific row from the data model environments.
Mike, I've been your biggest fan for years, but I have been so confused by the video FOR YEARS. I think your variable names should be OR (Outside RECORD) and IR (Inside RECORD) because the each function doesn't represent the Outside TABLE, but it represents the Outside RECORD from the table. It literally took me two years to figure that out. That the custom variable name doesn't represent a table, but it represents a RECORD. I finally figured it out tonight by typing =Table.AddColumn(BufferedTable, "RT", (OT)=>OT).
I agree. If you use each _ each means every item in the collection and the _ is context sensitive. When working with a table the _ represents each row of the table which of course is a set of records. If you're working with a list the _ represents each item of the list. Same goes for records.
Hi Mike.. jaw dropping open.. haha!! You are such a master at solving these problems every which way. Absolutely love the worksheet formula.. so elegant and efficient using expandable ranges and EOM() function with a -1 and no need to trap for upper limit.. genius! The PQ M Code and DAX solutions are equally awesome! Thanks again for sharing your incredible skill and creativity which helps all of us get a little better with each video. Thumbs up!
Mike I have been learning through your videos since 2011, superb learning, you are part of the life.....Just want you to give solution rather than dates, within items how can we get running totals RT and RT(Group), I think all we need is to add helper column with index and then apply same logic for dates on index....just want to see in your video
Mike, what a great video! And what amazing solutions! You’re the best! :-) BTW: Holy crap! I thought this was a simple question! LOL! ;-) The amazing thing is that IMO you actually used some DAX-like reasoning in all three environments, which makes it easy to compare the three solutions. And you didn’t use any special functions like rankx or topN, like I saw in other videos. Furthermore: this is the first time I see a DAX-“Evaluation Context”-like solution in PQ-M - that’s awesome! I really think you should add this topic of running totals to the MSPTDA series because both the PQ and the PP solutions warrant it! (And we get the Excel Classic solution as a bonus). Just my 2 cents. Furthermore, I saw what you did there: earlier (pun intended) I asked for more exposure of some minor but useful topics like “Reference” in PQ, and you snuck them in as well. Finally, thanks for referring to my question. I much appreciate it. PS: you sounded a bit tired - now you take it easy coming weekend, OK? See you later!
DAX-like reasoning... I thought I used Expandable Range-like reasoning in the video... But it doesn't matter what name we give it, it is fun to try and do it the three different tools. The DAX and M Code both require the whole table and filtering to get that expandable range, whereas the Excel Formulas, we just use the correct $ sign and it actually expands : ) We used Dates here so it made sense to use FILTER rather than RANKX or TOPN. I guess that might be a different running total situation where those could come in handy. What is the link to videos where you have seen those used? As for M Code iterating down a column, it has always been like that, but people do not usually use the DAX iterating language as often, so I had to mix it up and use some words in DAX that I usually use in M Code and visa versa : ) But yes, both M Code and DAX iterate down columns. I actually use a few of the Excel Magic Tricks in my MSPTDA class here at Highline Colllege. You are very correct that they should be part of MSPTDA. Yes, I snuck a lot of topics into this video: M Code Custom Functions, Field Access Operators, Expandable Range Concept, DAX Variables and more... You are welcome, Geert! It is great to hang out on our Online Excel Team with you. Yes, I am tired, and more tired as time goes on. I have been working 10-16 hours a day, almost all days in the years, all the way back to the 1980s... 11 years here at TH-cam, none stop... But, making these video works of art about how to have fun and power with Worksheet, Power Query and DAX keeps me going : ) : ) Go Team!!!!
Mike, you’re absolutely right: “expandable ranges” IS the theme, here! And you clearly demonstrated and narrated that. Since right now I’m still working on my multi-file, multi-table per file dashboard and I’ve arrived in the DM (Data Model) stage of the development (again - having thrown around the data model for the 8th time now), I seem to be looking at things from a DAX perspective more than usual, even more so since I recently pulled the trigger on the Ultimate Guide To DAX by Russo and Ferrari, earlier this week... Another reason why it resonated DAX with me is the fact that both your PQ-M and DM-DAX solutions use nested contexts -again- something I hadn’t seen in PQ-M before - very elegant. It seems that we have something else in common, too: we’re both workaholics, but I do advise you to unwind during the weekends (if possible) because human minds are more creative when rested, even competitive minds like yours. ;-) I’ll look up that other video and post the link (but mind you: the level of explaining you’ll see is really sub par compared to your work).
Mike, here are the other videos I was referring to. Mind you: their teaching styles are underwhelming, to put it mildly (don’t copy their styles! Just look at the formulas): Enterprise DNA: th-cam.com/video/f5k4fD1YJrQ/w-d-xo.html Curbal: th-cam.com/video/d-Jk1j2G3nw/w-d-xo.html
Not always the modern functions are also better... in this case, unless there is a need to use the calculations made in M or DAX in subsequent calculations, I'll have no doubt and use the classic Excel formulas... much easier!
I agree. As you as, when there is a need, we might have to use these other methods. We are lucky to have so many tools: Excel = small data, data in Excel, Worksheet is sufficient, Power Query = External data sources, need to transform for data modeling, DAX & Data Model = Big Data and want DAX functions, Power BI = Interactive & easily Shareable Visualizations : ) All 4 are amazing and fun tools we have in 2019 : )
Mike, good duel of applying the same logic across different methods. But I don't like DAX queries as a report's output because they don't allow drilling down the analysis. Power Pivot's superiority is its power to further analyze data by drilling and slicing.
Yes, it is a lot of fun for viewers and for me to do the same thing across the different tools : ) What do you mean can't drill down? Do you men in a Data Model PivotTable?
@ExcelIsFun (I suspect that a possible root of confusion is caused when terminology is used differently from how it's defined by Microsoft. Since another video of yours acknowledged being corrected about a terminology as used by Power BI Service, I raise as an aside that this video's reference to "custom function" is a terminology reserved by Power Query for functions invoked by Function.Invoke in M formula language. In Excel though, the same terminology is defined differently because Microsoft's own definitions can conflict in different environments even when they’re trying to be consistent!) My comment is referring to the terminology as defined at support.office.com/en-us/article/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045 . In Power Pivot, a matrix enables drilling down of hierarchies that can't be done in a table. In Power Pivot, DAX queries were exemplified in EMT 1435.
@@excelisfun Sir i have stuck during learning in one senerio. In my scenario i have to give rank or top N to a MEASURE. This measure is cumulative percentage of a product in last six month. I have product column , months columns from my table and a MEASURE of cumulative percentage of a product in each month. Now i have find out which month of each product has highest percentage. I tried a lot but not successful yet. If would appreciate a lot if you share your knowledge. Thanks Take Care
I am happy to hear that they are well explained for you, Ismaill!!! It is fun for me to try and create videos that help make complicated topics less complicated : )
Thanks Mike, straight to favorites. Took me long long time to learn these. One of the problems was with big data, you have to use pivot tables. The issue was filtering by date and date hierarchy i could not figure out the formula for visible cells only (filtered cells).
Great video as always! I’m hoping MS finds a way to calculate running totals easier for users in PQ. SQL window functions make this very easy to do w/ a much easier learning curve on the syntax.
Yes, the topic we commented about the other day, brought to video life. Lucky, you provided insight to help make this video, RRR!!!! Thanks you so much : ) Go Team!!!!
This is indeed a good solution. However, if a running total is required to be grouped by item or subcategory, what would be the syntax ? Is there any similar video explainin the same for excel power query as well as in excel DAX ?
Hi Mike, Wow, I'm super impressed by all the details you managed to put in this video, I will have to re-watch it! ;-) I imagine that the DAX way is more memory efficient and faster with big data? The M codes are splendid, inspired by Mr Szysz? :-) Thank you so much for these gold nuggets!
Yes, I was surprised how it came out and how much detail was in it. I started filming and editing and I just kept find opportunities to show cool stuff. It is often that way when I create videos: when I start to film and edit, I have a plan, but no idea where the story will actually go. Yes, I imagine that DAX would be more efficient on Big Data because of the Columnar Database and the fact that many DAX formulas work on that reduced size Columnar database. And, yes, I am ALWAYS inspired by Bill Szysz!!!! I have learned Power Query from Bill Szysz, reading the Power Query Formula Guide and Practice, Practice, Practice... I have never met or seen anyone who knows Power Query as well as our Teammate Bill Szysz!!!
@@excelisfun Hi Mike, Just re-watched... ;-) Still awesome! In one of my query I use something similar using EACH on a mini table from a MERGE operation: nc_nb_fr = Table.AddColumn(nc_locs, "nb_FR", each List.Count(Table.SelectRows([all], each [storage_description]="Flowrack")[location_id])) But what a learned from your video is how to use it on the same table with table.buffer, really great trick. Excel can be so exciting sometime! ;-) Here's another bunch of Gold Nuggets for you, well deserved, as usual!
@@excelisfun Hello there are two rows. the top row has numbers and letters and the bottom row has numbers from 1-11 1 represents number 3, 2 letter v , 3 letter t...... which video can you direct me to which shows how to select a number from the bottom row and then automatically the number/letter is highlighted from the top row ? conditional formatting ? so if i select number one, then in the top row, the 3 is highlighted. would i need an on/off formula?
Great footage, Mike! Well done... but I have a question. How can we set a running total for negative numbers, jump out of the loop when the numbers are positive and re-sum the total when they are negative again? Btw, I am a trader and I am trying to count the biggest drawdowns frm my testing
Awesome Sir... Want to be your student / assistant and learn to think like you... ☺️ The way you use the features of tech is super cool... Genius... Will I get a chance to work with you
Well, we can work as a Team: I will create videos, files, notes and more for you and others to learn, and you can watch, learn, have fun, and comment and thumbs up each video : ) Sound like a good deal, Sanju?
Hi Mike, thank you ever so much for the solution, and for the clear explanation of every step! I've one question: if I wanted to add one or two more conditions to the code, beside the date, where'd I put them? For example, I'd like to have the running total based on date, project number, and colleague name. What do you think?
Brilliant as ever, has anyone used the 'Earlier' in creating a measure to give a running total, I got the 'variable' option to work but not with 'Earlier'.
Amazing. Excel and DAX methods seemed easier for me to understand, PQ? Need to rewind again lol Would like to see running total by a fiscal quarter and fiscal year too ;)
Power Query is much different than Excel and DAX. Excel is Excel, Power Query is like SQL and DAX is like Excel, DAX and Columnar Database... Thanks for hanging out and having fun and supporting, Sal A!!!
Thank you so much, very helpful video. I have a question, i have following structure: Date, Product code, Result, Schedule Day1, A, 0, 0 Day2, A, 20, 100 Day3, A, 200, 100 How can i add a new column to get a running [Different], which reset by product code Date, Product code, Result, Schedule , Different Day1, A, 0, 0 , 0 Day2, A, 20, 100 , -80 Day3, A, 200, 100, 20 Where Different = previous's day different +result - schedule Thank you in advance for any help for this.
I just did a video on that exact topic one week ago!?!? Are you Subscribed and did you click the Bell Icon to get notifications of new videos?. Anyway, here is the link for you: th-cam.com/video/eoAsoLSm9DQ/w-d-xo.html I can't wait for you to watch and comment bellow the video and say if this helps : )
I have a column of zeroes and ones. Do you have any Power Query suggestions for "resetting" the running total when a zero appears? I am trying to get a running total of the ones, with the counter starting back at zero when a zero value appears. I can do this fairly easily in a table on a spreadsheet, but would like to do in power query. Have been trying to create some sort of "IF/THEN" approach with no success. Great video!
Dear Mike, thanks a lot for your perfect lessons. I have the challenge that I only get monthly cumulative ytd values / running totals (oct. = new FY) and I do not find a nice pq or power pivot solution to get the monthly values. Could you please so kind and help? Thanks a lot and kind regards
Thank you Mike, simple question, complex solution. I have a different one: why not combine the PQ tool and an Excel function? I used PQ to get the data back to Excel, but with a step to sort the dates values, and then....just add the two collumns in as you did !! this works fine. Attention you can't enter the cell by clicking on it, you have to manually enter the adress. Please let me know what you find of this solution.
@@excelisfun You are slow? you are kidding me... Anyway I will try again. you use PQ to grab the columns B and C (data and units) and put it back to Excel. Nothing exiting about this. But you can add a PQ step to sort the date. So on the "green" result of the PQ table (this is again a table) you can add a new column (on the right side attached) (just type it) and enter the formulas you did in column E and F. (you know that you can't reference a cell with the mouse but you should enter it) .So now if you add new data in you original table (B and C) , refresh and the magic happens....;) No need for complex m code... In some situations this is a fine method...
Glad it helps, david : ) Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
Glad it helps, Hendrick!!!! Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
Great video, as usual, Mike! Any hints on how to alter this from "Running total by month" to "Running total for last 30 days"? I figured out the formula solution [easy], but not the PQ method. CalBeMe 1 second ago
That would be a different video topic. But I guess in PQ we could use the Date.AddDays function for the lower limit and in DAX we could use a variable for 30 days back lower limit like: Var ThirtyDaysBack = RTDAX[Date]-30
Mike, I'm trying to get a running total using an index column, instead of dates. I think I used the same logic in my formula as you did -> .However, the calculation is taking very long to run: I'm iterating over 157,000 rows, so I don't know if that could be a problem or not, but it would be very nice of you if could you give me some advice.
@@Torn85 Wow, I really do not know. That doesn't seem like it should be that slow with only 200 K. Can you post to the Mr Excel Message Board Power BI section: www.mrexcel.com/forum Then post back the link that you post so I can learn too. There is a a guy there that sometimes has really good answers named Matt Allington. He has helped me a few times.
Mike, thanks for the helpful link to what seems like a great resource. Here is my post: www.mrexcel.com/forum/power-bi/1115134-slow-calculation-calculated-column-power-pivot.html#post5371772
Congratulations. It's a great video to understand how it works the data model. I wonder if it would be possible to take advantage of the power of Dax in Power Pivot inside PowerQuery. For example, sometimes you wish the maximum of a column. It's easy in Dax, but not in Powerquery. So, could it be possible to use any measure defined in Dax in the M code of PowerQuery using in same way the Data Model? This would be Dax & M working together over the same table... is it a crazy idea?
@@excelisfun What it is my mind is not to use use DAX inside M but to take advantage of the data model and to perform some calculations in Power Pivot (Dax) on a table and then to "get data" from that table from Powerquery..... Do you believe that such combination would be possible? Dax is better to deal with measures than PQ
@@JamesManjackalMSFS I am sorry, but i do not follow what you are saying, asking... The combination of Power Query and Data Model and DAX is very strong, in many ways, though...
Doesn't M already have functions to find the Max and Min of Tables and Lists? I believe it also has similar functions to find the top N biggest and smallest values as well. The problem is that M is so poorly documented that people don't realize how powerful it is. The best thing to do is create a blank query and then enter =#shared which will give you a list of all of the functions, etc. You're bound to find whatever it is you're looking to do.
@@brianxyz This is an example th-cam.com/video/fFvJ3JtITyc/w-d-xo.html
ETL is done with PowerQuery, then to save everything in the model as connections .... then open Power Pivot and the rest is done .... What I would like is to hava unity. To press a push button and to perform all PQ+PPivot. I eman that if there is an update in the source file, then that everything could be done only once when refreshing tables ... i.e. update PQ and run Power PIvot. Only one step...
How would I amend the DAX Expression where the calculation needs to reset for a change in an item list and not the date range? The Use Case is projected inventory closing stock by item considering a starting on hand inventory count (today) plus pending inbound supply (tomorrow and beyond) minus the pending demand value (tomorrow and beyond).
How would I write that if I needed to use part number? I have repeating part number values and routing steps but I don't think I need the routing steps just the part number as a unique identifier.
Trying to figure out how to use this to calculate a LTM, L6M.... can't seem to get the RTM to break it down to it's specific categories either, I guess I should be adding an extra filter. One day!
I have spend data in months and by Cost center and Cost element. This data has been loaded into power query but i need a pivot table report that is should give me a ytd total for each cost center and cost element based on filtered period. ie if select period 2, pivot table should give the summary of all spend for period 1 and 2 by cost center and cost element. Any help?
Hello Mike, I try today this (OT) (IT) on a file I have at work that was more than 200k lines. The computer did not finish to calculate when I left (even if I buffered the Table). (OT) (IT) concept require a lot of resources. On the way home I thought that it could be done in a different way in PQ. why not, step 1 sort by date, step 2 add an index, step 3 group by month, step 4 integrate an sub-index(start on 0), step 5 expand the grouping. step 6 buffer the table step 7 add a column RT with List.Sum(Table.Range(“bufferedtable”, [Index] - [sub-Index] , [Index]+1))[Units]) Would it not be more performance effective ?
he he, , you can forget my comment above ;) I come to make a trial on a 2000 line data set, my proposal was not so effective neither. I did not chose the DAX solution because my running total was group on 2 parameters, But finally succeed to do it in DAX Running total := VAR currentindex = [Index] VAR currentcontract = [contract] VAR currentpartno = [Part no] RETURN CALCULATE( SUM('Table_benchmark by DAX'[Units]); FILTER( ALL('Table_benchmark by DAX'); [Index]
@@Anthony_Lecoq And there is the magic of DAX: it is amazingly fast. Thanks for testing out M Code and DAX. That is some good looking "VAR Happy" DAX code you made there, anthony : )
How come Outside table (OT) automatically gets detected as the current record, and why do we need to define inside table (IT) at all inside table.selectrows, meaning why wont [Date]
I can't because I am not very good with VBA. I have spent my cognitive energies for 20+ years learning and teaching advanced formulas, Power Query, DAX ... Sorry, but I am not smart enough to hep you with a VBA channel.
Topics:
1. (00:07) Introduction
2. (01:20) Excel Worksheet Formula solution using SUMIFS and EOMONTH functions with an Expandable Range. Two formulas: Overall Running Total and Running Totals by Month.
3. (04:32) Look at expandable range in a Worksheet Formula
4. (05:18) Import Table for both Power Query Solution and DAX Solution using Power Query and the Reference feature.
5. (06:50) Power Query Custom Column with Table.AddColumn Function, Table.SelectRows Function and Two M Code Custom Functions to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column.
6. (08:15) Lesson about how Custom Columns and M Code Custom Functions work, including the how the keyword “each” is a substitute for an explicitly defined Custom Function.
7. (13:33) Look at expandable range in a Power Query Formula.
8. (14:25) Field Access Operator for Looking up: 1) A Full Column or 2) A Single Item from Each Row.
9. (20:01) DAX Calculated Column with the functions CALCULATE, SUM, FILTER, EARLIER, EOMONTH and a Var Variable to create expandable ranges that can calculate a Overall Running Total and Running Totals by Month in a Column.
10. (26:46) How to use a Variable in DAX. See Var = Mon Return syntax for a Variable in DAX.
11. (30:26) Summary
Excellent. I know this is more work to do, but help us a lott!!!
@@jimmyni1983 You are welcome! Thanks for your support : )
Great video Sir, Can we do for week level also ?
Thank you.
I spent almost a day trying to correct the following formula
=CALCULATE(
SUM(Table1[Amount]),
FILTER(ALL(Table1[Date]),
Table1[Date]
You are welcome, Ajay!!!
Can I hit the like button 10000000000000000 times? Pleaseee! I simply love you guy! The best of the best!
Thanks for the 10000000000000000 times you hit that like, Jimmy!!!!!
Excellent teacher,
Excellent knowledge,
Excellent video,
Thanks for sharing.
Glad the videos helps : )
Your teaching is superb! Thank alot.
You are welcome a lot!
I want to say thank you to you. After following your youtube videos for 1 year, I have learned so much and found a great job doing data analysis in a great company. thank you so much!
You are welcome! I am so glad that I can help you to do Data Analysis in your new job : )
Very insightful video Mike, I really need to re-watch it to grasp its ideas. Actually, I've done it in an alternative way using PQ, but for running total restart by each store from transaction table as follows:
0- Create query from table then correct "Date" to type date.
1- Reference step 0 as new query
2- Sort by store & date
3- Add 0 index
4- Group by store for min of index
5- Merge #"Added Index" with above step based on store
6- Expand [Grouped Rows] to [min]
7- Resort by index (as expand disturb the order)
8- Add custom column =List.Sum( List.Range( #"Sorted Rows"[Amount], [min], [index] - [min] +1)
Initially, I buffered step 1, despite that it was taking long time to refresh with +1000 record. But after seeing your video I buffered step 7 instead and it does drastically help. As beginner-to-intermediate user, it is not clear what actually Table.Buffer does and when & at what step to use it. Also is there a more efficient way to do vertical calculations (e.g running total) in PQ?
I got more than 10 techniques from this video. Previously, EARLIER made me confuse, but you make me truly understand.
Thanks a lot.
You are so welcome!!!!! It is always nice when something that confused you, no longer confuses you : )
A thousand thanks, Mike! This is a beautiful treatment of iterative functions and power query. In all my reading and research, I found nothing that more clearly illustrates both the concepts and the practice. Many thanks again!
You are welcome, Jeff!!!! Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
Thank you very much for all the info you shared in the video
You are welcome!!!
Thank you for this, I personally found the DAX code easier to understand. This has solved a problem I've had that googling for days hadn't been able to fix.
Hi Mike, I have never thought to use table.selectrows and customer M code to do running total. I always add index starting from 1 then use List.sum and List.FirstN to do it. Learn something new today. Awesome!
I guess both ways are fine, though. Something new is alwasy good : ) Thanks for your support, sherry !!
This course is really one of your best Mike
15:30 love that nugget about the differences of each verses field access operator on an entire objects, in that case the derived column. Great video thanks Mike!
Glad you like it, Joshua!!!
Thank you very much for sharing your God's gifted talent with the global community.
OMG was this ever good. I learned so much from this.
You're such a great teacher....each minute that goes by I'm getting so much info...
I'm literally "learning by the minute" watching your content.
Keep it up!
🌝
Yes, sir! I am glad that you have found some other content that is useful to you. As I mentioned, I had a family tragedy and the MECS series is only progressing at a slow pace. It will be a full class - the best I have ever made - in the near future!!!
This is the most impressive excel/PQ video i have seen in years..
you’re real magician. 1000 thanks!
1000 You are Welcomes!!!!
The way that you are demonstrating is really elegant superb
Glad you like it, Casper : )
This is SO amazing!! Love it. I was able in Power Query to do a nested running total within another column just by adding " and IT[column]=OT[column]" directly after the
I'll watch it later when I get to that advanced level but I'm pretty sure it's Brilliant and fascinating as you always do!
Thanks, Mostafa!!!! Yes, this one is quite advanced : )
Amazing! This video helped me solving a hard difficulty that bothered me a dew days. I'm relieved now...thank you!
Glad you are relieved now!!! Thanks for your support, tingting, with your comment, thumbs up and your Sub : )
This is the best video I see about this topic! I had found another video with another custom function, but I think your solution is MUCH BETTER! And you explained all the way ( starting from Excel was a great teaching technique) !
I had two channels and you deserve 2 subscriptions!
All the best from Brasil!
Excel fun is great place to learn advanced excel
Thanks a lot
Dev from India (Kedarnath)
You are welcome a lot!! Thanks for your support with your comment, thumbs up and your Sub : )
Awesome tutorial & perfect timing, Mike! I'm working on a fairly robust inventory status & production planning workbook that includes calculation of a projected date on which existing inventory will be exhausted based on consumption in accordance with the current production plan (which changes weekly or more frequently). Sort of a reverse running total :-) I knew how to do this in Excel with formulas, but wasn't sure if/how to do this in PQ (preferred) or DAX (if needed). I took a break for lunch & figured I'd hunt for some training on the topic. (My kids would tell me to take a real break, but - hey! - I don't pick their entertainment.) Of course, ExcelIsFun was my first choice from the search results. Spot on! I'm looking forward to building this technique into my models. Thanks!
BTW, forgot to endorse Geert's excellent suggestion to add this to the MSPTDA series.
@@jimfitch I added it to the MSPTDA playlist!!!
I love to hear that this helped with your solution!!! Can you send me the workbook when you get the final solution, so AI can take a look and admire: excelisfun at gmail : )
Buffer and OT/IT comes again, finally this time I can understand how it works, while still far away from use this type of code in M code. And the VAR is new in DAX , interesting to know it can be used in such way , great video , keep learning ~
I am so glad that my classes help you - thanks for your support on each video, zhou : )
Thank you Mike, always getting so much fun and value from watching your videos. Have a great day Mike
🤗 🌞
You are welcome, Katerina : )
This was an epic video! Really cool series of tricks between Power Query and Power Pivot. It was an extremely clear explanation of iterating functions and generating a specific row from the data model environments.
Glad you like it , TP : ) : ) : )
You are amazing Mike. I still need to learn more about Power Query. It is so powerful and you explain it very well. Thank you so much.
You are welcome so much, K D!!!!
Mike, I've been your biggest fan for years, but I have been so confused by the video FOR YEARS. I think your variable names should be OR (Outside RECORD) and IR (Inside RECORD) because the each function doesn't represent the Outside TABLE, but it represents the Outside RECORD from the table. It literally took me two years to figure that out. That the custom variable name doesn't represent a table, but it represents a RECORD. I finally figured it out tonight by typing =Table.AddColumn(BufferedTable, "RT", (OT)=>OT).
I agree. If you use each _ each means every item in the collection and the _ is context sensitive. When working with a table the _ represents each row of the table which of course is a set of records. If you're working with a list the _ represents each item of the list. Same goes for records.
Hi Mike.. jaw dropping open.. haha!! You are such a master at solving these problems every which way. Absolutely love the worksheet formula.. so elegant and efficient using expandable ranges and EOM() function with a -1 and no need to trap for upper limit.. genius! The PQ M Code and DAX solutions are equally awesome! Thanks again for sharing your incredible skill and creativity which helps all of us get a little better with each video. Thumbs up!
You are welcome, Wayne!! Thanks for watching and having fun : )
Mike I have been learning through your videos since 2011, superb learning, you are part of the life.....Just want you to give solution rather than dates, within items how can we get running totals RT and RT(Group), I think all we need is to add helper column with index and then apply same logic for dates on index....just want to see in your video
Mike, what a great video! And what amazing solutions! You’re the best! :-)
BTW: Holy crap! I thought this was a simple question! LOL! ;-)
The amazing thing is that IMO you actually used some DAX-like reasoning in all three environments, which makes it easy to compare the three solutions. And you didn’t use any special functions like rankx or topN, like I saw in other videos.
Furthermore: this is the first time I see a DAX-“Evaluation Context”-like solution in PQ-M - that’s awesome!
I really think you should add this topic of running totals to the MSPTDA series because both the PQ and the PP solutions warrant it! (And we get the Excel Classic solution as a bonus). Just my 2 cents.
Furthermore, I saw what you did there: earlier (pun intended) I asked for more exposure of some minor but useful topics like “Reference” in PQ, and you snuck them in as well.
Finally, thanks for referring to my question. I much appreciate it.
PS: you sounded a bit tired - now you take it easy coming weekend, OK?
See you later!
DAX-like reasoning... I thought I used Expandable Range-like reasoning in the video... But it doesn't matter what name we give it, it is fun to try and do it the three different tools. The DAX and M Code both require the whole table and filtering to get that expandable range, whereas the Excel Formulas, we just use the correct $ sign and it actually expands : )
We used Dates here so it made sense to use FILTER rather than RANKX or TOPN. I guess that might be a different running total situation where those could come in handy. What is the link to videos where you have seen those used?
As for M Code iterating down a column, it has always been like that, but people do not usually use the DAX iterating language as often, so I had to mix it up and use some words in DAX that I usually use in M Code and visa versa : ) But yes, both M Code and DAX iterate down columns.
I actually use a few of the Excel Magic Tricks in my MSPTDA class here at Highline Colllege. You are very correct that they should be part of MSPTDA.
Yes, I snuck a lot of topics into this video: M Code Custom Functions, Field Access Operators, Expandable Range Concept, DAX Variables and more...
You are welcome, Geert! It is great to hang out on our Online Excel Team with you.
Yes, I am tired, and more tired as time goes on. I have been working 10-16 hours a day, almost all days in the years, all the way back to the 1980s... 11 years here at TH-cam, none stop... But, making these video works of art about how to have fun and power with Worksheet, Power Query and DAX keeps me going : ) : ) Go Team!!!!
Mike, you’re absolutely right: “expandable ranges” IS the theme, here! And you clearly demonstrated and narrated that.
Since right now I’m still working on my multi-file, multi-table per file dashboard and I’ve arrived in the DM (Data Model) stage of the development (again - having thrown around the data model for the 8th time now), I seem to be looking at things from a DAX perspective more than usual, even more so since I recently pulled the trigger on the Ultimate Guide To DAX by Russo and Ferrari, earlier this week...
Another reason why it resonated DAX with me is the fact that both your PQ-M and DM-DAX solutions use nested contexts -again- something I hadn’t seen in PQ-M before - very elegant.
It seems that we have something else in common, too: we’re both workaholics, but I do advise you to unwind during the weekends (if possible) because human minds are more creative when rested, even competitive minds like yours. ;-)
I’ll look up that other video and post the link (but mind you: the level of explaining you’ll see is really sub par compared to your work).
@@GeertDelmulle , Yes, good fun on the weekend is great - I usually race BMX bicycles with my son on the weekends... Go Team!!!!
Mike, here are the other videos I was referring to. Mind you: their teaching styles are underwhelming, to put it mildly (don’t copy their styles! Just look at the formulas):
Enterprise DNA:
th-cam.com/video/f5k4fD1YJrQ/w-d-xo.html
Curbal:
th-cam.com/video/d-Jk1j2G3nw/w-d-xo.html
BTW, here’s another channel I do like - good style and knowledge (although some elements I find cheesy):
th-cam.com/users/Officeinstructor
Not always the modern functions are also better... in this case, unless there is a need to use the calculations made in M or DAX in subsequent calculations, I'll have no doubt and use the classic Excel formulas... much easier!
I agree. As you as, when there is a need, we might have to use these other methods. We are lucky to have so many tools: Excel = small data, data in Excel, Worksheet is sufficient, Power Query = External data sources, need to transform for data modeling, DAX & Data Model = Big Data and want DAX functions, Power BI = Interactive & easily Shareable Visualizations : ) All 4 are amazing and fun tools we have in 2019 : )
Great video Mike!!
Thanks, Chris!!!! It is great to be on a Team with you : )
I love the way you did running total because you are not use parameter function. Can you teach fill down conditions using power query?
Mike, good duel of applying the same logic across different methods. But I don't like DAX queries as a report's output because they don't allow drilling down the analysis. Power Pivot's superiority is its power to further analyze data by drilling and slicing.
Yes, it is a lot of fun for viewers and for me to do the same thing across the different tools : ) What do you mean can't drill down? Do you men in a Data Model PivotTable?
@ExcelIsFun (I suspect that a possible root of confusion is caused when terminology is used differently from how it's defined by Microsoft. Since another video of yours acknowledged being corrected about a terminology as used by Power BI Service, I raise as an aside that this video's reference to "custom function" is a terminology reserved by Power Query for functions invoked by Function.Invoke in M formula language. In Excel though, the same terminology is defined differently because Microsoft's own definitions can conflict in different environments even when they’re trying to be consistent!)
My comment is referring to the terminology as defined at support.office.com/en-us/article/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045 . In Power Pivot, a matrix enables drilling down of hierarchies that can't be done in a table. In Power Pivot, DAX queries were exemplified in EMT 1435.
Extremly useful topic! Thanks for sharing, Mike :). Awesome as always! :)
You are welcome, Malina!!
Always get Tons of knowledge from you, thks
You are welcome, Sira! Thanks for your support : )
Sir, This video gives answers of many questions.
Thanks
Most welcome!!!!
@@excelisfun Sir i have stuck during learning in one senerio.
In my scenario i have to give rank or top N to a MEASURE. This measure is cumulative percentage of a product in last six month. I have product column , months columns from my table and a MEASURE of cumulative percentage of a product in each month.
Now i have find out which month of each product has highest percentage.
I tried a lot but not successful yet.
If would appreciate a lot if you share your knowledge.
Thanks
Take Care
Thanks Mike for this EXCELlent video.
You are welcome, Syed : )
Congratulations. Thank you Mike!!!
You are welcome, Luciano!!!!
the best about your videos that are well explained
I am happy to hear that they are well explained for you, Ismaill!!! It is fun for me to try and create videos that help make complicated topics less complicated : )
As usual Mike.. Extremely AWESOME...
Extremely fun, right Sayen?
Thanks for your support with your comment, thumbs up and your Sub : )
So intense.....but Fun. Thanks Mike.
You are welcome, John!
Thanks Mike, straight to favorites. Took me long long time to learn these. One of the problems was with big data, you have to use pivot tables. The issue was filtering by date and date hierarchy i could not figure out the formula for visible cells only (filtered cells).
I am sorry, but I do not understand what you are trying to communicate...
Great video as always!
I’m hoping MS finds a way to calculate running totals easier for users in PQ. SQL window functions make this very easy to do w/ a much easier learning curve on the syntax.
Wow, beautiful stuff. Thanks Mike
Glad it is beautiful for you, nimrodzik1!!!
Hi Mike, I absolutely loved the SUMIFS and EOMONTH combo; amazing thanks for sharing this.
You are absolutely welcome, Sachin!!!!
Awesome mike.... This is the same topic we discussed the other day...
Yes, the topic we commented about the other day, brought to video life. Lucky, you provided insight to help make this video, RRR!!!! Thanks you so much : ) Go Team!!!!
@@excelisfun absolute pleasure to work with excelisfunnnn
@@rrrprogram8667 Go Team!!!!!
You are the best! thank you!
thanks for sharing triple fun in a single video.
You are welcome for the triple fun!!!
Amazing comparison!
This is indeed a good solution. However, if a running total is required to be grouped by item or subcategory, what would be the syntax ? Is there any similar video explainin the same for excel power query as well as in excel DAX ?
Hi Mike,
Wow, I'm super impressed by all the details you managed to put in this video, I will have to re-watch it! ;-) I imagine that the DAX way is more memory efficient and faster with big data? The M codes are splendid, inspired by Mr Szysz? :-)
Thank you so much for these gold nuggets!
Yes, I was surprised how it came out and how much detail was in it. I started filming and editing and I just kept find opportunities to show cool stuff. It is often that way when I create videos: when I start to film and edit, I have a plan, but no idea where the story will actually go. Yes, I imagine that DAX would be more efficient on Big Data because of the Columnar Database and the fact that many DAX formulas work on that reduced size Columnar database. And, yes, I am ALWAYS inspired by Bill Szysz!!!! I have learned Power Query from Bill Szysz, reading the Power Query Formula Guide and Practice, Practice, Practice... I have never met or seen anyone who knows Power Query as well as our Teammate Bill Szysz!!!
You are welcome for the Gold Nuggets, Eric : )
@@excelisfun
Hi Mike,
Just re-watched... ;-) Still awesome!
In one of my query I use something similar using EACH on a mini table from a MERGE operation:
nc_nb_fr = Table.AddColumn(nc_locs, "nb_FR", each List.Count(Table.SelectRows([all], each [storage_description]="Flowrack")[location_id]))
But what a learned from your video is how to use it on the same table with table.buffer, really great trick. Excel can be so exciting sometime! ;-)
Here's another bunch of Gold Nuggets for you, well deserved, as usual!
@@EricGiroux Yes, Sir!!!! I completely agree: Excel can be so exciting sometimes!!!!! Thank you for the gold nugget bunch : )
@@excelisfun Hello
there are two rows. the top row has numbers and letters and the bottom row has numbers from 1-11
1 represents number 3, 2 letter v , 3 letter t......
which video can you direct me to which shows how to select a number from the bottom row and then automatically the number/letter is highlighted from the top row ? conditional formatting ?
so if i select number one, then in the top row, the 3 is highlighted.
would i need an on/off formula?
Great footage, Mike! Well done... but I have a question. How can we set a running total for negative numbers, jump out of the loop when the numbers are positive and re-sum the total when they are negative again? Btw, I am a trader and I am trying to count the biggest drawdowns frm my testing
Awesome Sir...
Want to be your student / assistant and learn to think like you... ☺️ The way you use the features of tech is super cool... Genius...
Will I get a chance to work with you
Well, we can work as a Team: I will create videos, files, notes and more for you and others to learn, and you can watch, learn, have fun, and comment and thumbs up each video : ) Sound like a good deal, Sanju?
Nice topic for video
Glad you like it, Akshay! Thanks for your support : )
Hi Mike, thank you ever so much for the solution, and for the clear explanation of every step!
I've one question: if I wanted to add one or two more conditions to the code, beside the date, where'd I put them? For example, I'd like to have the running total based on date, project number, and colleague name. What do you think?
Brilliant as ever, has anyone used the 'Earlier' in creating a measure to give a running total, I got the 'variable' option to work but not with 'Earlier'.
Amazing. Excel and DAX methods seemed easier for me to understand, PQ? Need to rewind again lol
Would like to see running total by a fiscal quarter and fiscal year too ;)
Power Query is much different than Excel and DAX. Excel is Excel, Power Query is like SQL and DAX is like Excel, DAX and Columnar Database... Thanks for hanging out and having fun and supporting, Sal A!!!
Thank you so much, very helpful video.
I have a question, i have following structure:
Date, Product code, Result, Schedule
Day1, A, 0, 0
Day2, A, 20, 100
Day3, A, 200, 100
How can i add a new column to get a running [Different], which reset by product code
Date, Product code, Result, Schedule , Different
Day1, A, 0, 0 , 0
Day2, A, 20, 100
, -80
Day3, A, 200, 100, 20
Where Different = previous's day different +result - schedule
Thank you in advance for any help for this.
Very nice
Yes!!! I love you hear that it is very nice for you, chinna k : ) : )
Hi Mike! Is there any way to speed up / optimize performance of your function ?
Thanks a lot, very helpful. I have a question:
How to sum latest n record using DAX? For example the sum of latest 3 sales records.
I just did a video on that exact topic one week ago!?!? Are you Subscribed and did you click the Bell Icon to get notifications of new videos?. Anyway, here is the link for you: th-cam.com/video/eoAsoLSm9DQ/w-d-xo.html I can't wait for you to watch and comment bellow the video and say if this helps : )
Excellent trick for Power Query, thank you, Mike. Why can't we use Total YTD or MTD dax functions to achieve running total?
The case was to perform a Calculated column rather than a measure.
@@ricos1497 , That is correct : )
I have a column of zeroes and ones. Do you have any Power Query suggestions for "resetting" the running total when a zero appears? I am trying to get a running total of the ones, with the counter starting back at zero when a zero value appears.
I can do this fairly easily in a table on a spreadsheet, but would like to do in power query. Have been trying to create some sort of "IF/THEN" approach with no success.
Great video!
Very helpful! Thank you so much!
Glad it helps, Anchor!!!
ExcelIsFun I was wanting to use this to use datediff, however I am getting an error and not sure where to go.
@@anchorpoints5026 DATEDIFF is an undocumented function that sometimes has issues.
ExcelIsFun so, could I duplicate date field, covert to number and use like you do units using List.Difference?
Dear Mike, thanks a lot for your perfect lessons. I have the challenge that I only get monthly cumulative ytd values / running totals (oct. = new FY) and I do not find a nice pq or power pivot solution to get the monthly values. Could you please so kind and help? Thanks a lot and kind regards
Nice 👍
Glad it is nice for you, Qasr!!! It is great to see you in the comments on each video : )
Thank you Mike, simple question, complex solution. I have a different one: why not combine the PQ tool and an Excel function? I used PQ to get the data back to Excel, but with a step to sort the dates values, and then....just add the two collumns in as you did !! this works fine. Attention you can't enter the cell by clicking on it, you have to manually enter the adress. Please let me know what you find of this solution.
I am sorry, I do not follow your solution, Bart ... But I am slow to understand things, often... : (
@@excelisfun You are slow? you are kidding me... Anyway I will try again. you use PQ to grab the columns B and C (data and units) and put it back to Excel. Nothing exiting about this. But you can add a PQ step to sort the date. So on the "green" result of the PQ table (this is again a table) you can add a new column (on the right side attached) (just type it) and enter the formulas you did in column E and F. (you know that you can't reference a cell with the mouse but you should enter it) .So now if you add new data in you original table (B and C) , refresh and the magic happens....;) No need for complex m code... In some situations this is a fine method...
Thank you soooooooooooooo Much
Great video, thanks a lot.
You are welcome a lot, strateg.si! Thanks for supporting with your comment, thumbs up and your Sub : )
Great Video, thanks
You are welcome, Ogwal!!!
Brilliant!
Glad it helps, david : ) Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
Very informative :)
Glad it helps, Hendrick!!!! Thanks for your support on these videos that you learn from with your comments, thumbs up, and of course your Sub. It means a lot to me and helps grow the channel so that more people can find this free education : )
Great video, as usual, Mike! Any hints on how to alter this from "Running total by month" to "Running total for last 30 days"? I figured out the formula solution [easy], but not the PQ method.
CalBeMe
1 second ago
That would be a different video topic. But I guess in PQ we could use the Date.AddDays function for the lower limit and in DAX we could use a variable for 30 days back lower limit like: Var ThirtyDaysBack = RTDAX[Date]-30
Mike, I'm trying to get a running total using an index column, instead of dates. I think I used the same logic in my formula as you did -> .However, the calculation is taking very long to run: I'm iterating over 157,000 rows, so I don't know if that could be a problem or not, but it would be very nice of you if could you give me some advice.
Here is the formula that I used: =CALCULATE(SUM(LINES[CB Unit]),FILTER(LINES,LINES[Index]20 minutes) at 157,000 rows.
@@Torn85 Wow, I really do not know. That doesn't seem like it should be that slow with only 200 K. Can you post to the Mr Excel Message Board Power BI section: www.mrexcel.com/forum Then post back the link that you post so I can learn too. There is a a guy there that sometimes has really good answers named Matt Allington. He has helped me a few times.
Mike, thanks for the helpful link to what seems like a great resource. Here is my post: www.mrexcel.com/forum/power-bi/1115134-slow-calculation-calculated-column-power-pivot.html#post5371772
Thanks Mike
You are welcome, Dave!!!! : )
Congratulations. It's a great video to understand how it works the data model. I wonder if it would be possible to take advantage of the power of Dax in Power Pivot inside PowerQuery. For example, sometimes you wish the maximum of a column. It's easy in Dax, but not in Powerquery. So, could it be possible to use any measure defined in Dax in the M code of PowerQuery using in same way the Data Model? This would be Dax & M working together over the same table... is it a crazy idea?
I do not know how to use DAX in an M Code expression... But that would be cool, jlt!!!!!
@@excelisfun What it is my mind is not to use use DAX inside M but to take advantage of the data model and to perform some calculations in Power Pivot (Dax) on a table and then to "get data" from that table from Powerquery..... Do you believe that such combination would be possible? Dax is better to deal with measures than PQ
@@JamesManjackalMSFS I am sorry, but i do not follow what you are saying, asking... The combination of Power Query and Data Model and DAX is very strong, in many ways, though...
Doesn't M already have functions to find the Max and Min of Tables and Lists? I believe it also has similar functions to find the top N biggest and smallest values as well. The problem is that M is so poorly documented that people don't realize how powerful it is. The best thing to do is create a blank query and then enter
=#shared
which will give you a list of all of the functions, etc. You're bound to find whatever it is you're looking to do.
@@brianxyz This is an example th-cam.com/video/fFvJ3JtITyc/w-d-xo.html
ETL is done with PowerQuery, then to save everything in the model as connections .... then open Power Pivot and the rest is done ....
What I would like is to hava unity. To press a push button and to perform all PQ+PPivot. I eman that if there is an update in the source file, then that everything could be done only once when refreshing tables ... i.e. update PQ and run Power PIvot. Only one step...
عالی
I need to watch it several times! scarry!
Agreed! "Excel is Fun", or Excel (can be) complicated, but then I'm quite new to power table. I'm sure there's a learning curve there, obviously.
How would I amend the DAX Expression where the calculation needs to reset for a change in an item list and not the date range? The Use Case is projected inventory closing stock by item considering a starting on hand inventory count (today) plus pending inbound supply (tomorrow and beyond) minus the pending demand value (tomorrow and beyond).
How would I write that if I needed to use part number? I have repeating part number values and routing steps but I don't think I need the routing steps just the part number as a unique identifier.
Trying to figure out how to use this to calculate a LTM, L6M.... can't seem to get the RTM to break it down to it's specific categories either, I guess I should be adding an extra filter.
One day!
If sheet have 30k rows and 3 condition. It need the whole day. How to optimize performance
Smoke coming from my head.
That is a good sign : ) : )
Hi. Mike. Could you please make this Running total with. EOM. category. Sub category..? Tq
I mean RT with many category
I have spend data in months and by Cost center and Cost element. This data has been loaded into power query but i need a pivot table report that is should give me a ytd total for each cost center and cost element based on filtered period. ie if select period 2, pivot table should give the summary of all spend for period 1 and 2 by cost center and cost element. Any help?
Hello Mike, I try today this (OT) (IT) on a file I have at work that was more than 200k lines. The computer did not finish to calculate when I left (even if I buffered the Table). (OT) (IT) concept require a lot of resources. On the way home I thought that it could be done in a different way in PQ. why not, step 1 sort by date, step 2 add an index, step 3 group by month, step 4 integrate an sub-index(start on 0), step 5 expand the grouping. step 6 buffer the table step 7 add a column RT with List.Sum(Table.Range(“bufferedtable”, [Index] - [sub-Index] , [Index]+1))[Units])
Would it not be more performance effective ?
I am not sure? It sounds promising though, anthony!!! Please try it and then report back : )
he he, , you can forget my comment above ;) I come to make a trial on a 2000 line data set, my proposal was not so effective neither. I did not chose the DAX solution because my running total was group on 2 parameters, But finally succeed to do it in DAX
Running total := VAR currentindex = [Index]
VAR currentcontract = [contract]
VAR currentpartno = [Part no]
RETURN
CALCULATE( SUM('Table_benchmark by DAX'[Units]);
FILTER(
ALL('Table_benchmark by DAX');
[Index]
@@Anthony_Lecoq And there is the magic of DAX: it is amazingly fast. Thanks for testing out M Code and DAX. That is some good looking "VAR Happy" DAX code you made there, anthony : )
How come Outside table (OT) automatically gets detected as the current record, and why do we need to define inside table (IT) at all inside table.selectrows, meaning why wont [Date]
Thanks, a bit complicated, though.
Thanks for commenting and watching even though this is an advanced one : )
Indeed it is, but it tickles the imagination. Thanks again, Prof.
@@SaniGarba You are welcome, Sani!
👍
Can someone explain the EOMonth function :-(
Ok, Developers MUST create a direct RUNIING.TOTAL function, in MS excel or Power Query or DAX, or even in Nintendo games !!
Put simply... "each" means "each record"!
Please Make VBA channel
I can't because I am not very good with VBA. I have spent my cognitive energies for 20+ years learning and teaching advanced formulas, Power Query, DAX ... Sorry, but I am not smart enough to hep you with a VBA channel.