Power Query and Power Pivot to Automate FIFO calculations in Excel | DAX | Querinfer

You have always used excel templates for calculating cost of sales, closing inventory or number of items on hand. But if you have multiple products and so many transactions in one place, then the Power tools in Excel can help you in automating the First in First out allocation of sales items. For full course on Excel https://www.udemy.com/course/excel-from-scratch-to-pro-level/?couponCode=LETSLEARNNOWPP Learn how you can easily update your results in a Pivot Table report. Download link: https://drive.google.com/file/d/16sIlgnNbFiZoU_rJmM24TgLmxKjz2V81/view?usp=sharing In this amazing video learn following things one by one and be master of M language and DAX formulas: 00:00 Introduction 01:19 Understanding example of FIFO method 02:44 Sample Files 03:53 Insert Index Column for subgroups in Power Query (M) 09:02 Transfer table to PowerPivot 10:42 SUMX (DAX) with Filter for total sales 12:05 CALCULATE (DAX) for Running total of subgroups 16:41 CALCULATE (DAX) for previous row value for subgroups 18:46 LookUpValue (DAX) for searching subgroups value using Index 20:53 Final column for Allocation of sales to Lots 21:42 Calculating Cost of sales, closing shares 23:49 Measures Calculating revenue and profit 26:00 Create Pivot Table report 28:52 Testing Updating transaction table 31:41 Important message Download final complete file from: https://drive.google.com/file/d/1RugE4qWpCE48itYWhJITZp4ciKr85Svd/view?usp=sharing You can also go to my pages and check latest happenings in SHKExcelWorld: https://www.youtube.com/shkexcelworld?sub_confirmation=1 https://www.facebook.com/SHKExcelWorld https://www.linkedin.com/company/shkexcelworld