Power Query and Power Pivot to Automate FIFO calculations in Excel | DAX | Querinfer
28.2 هزار بار بازدید -
4 سال پیش
-
You have always used excel
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-fr...
Learn how you can easily update your results in a Pivot Table report. Download link:
https://drive.google.com/file/d/16sIl...
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/1RugE...
You can also go to my pages and check latest happenings in SHKExcelWorld:
https://www.youtube.com/shkexcelworld...
Facebook: SHKExcelWorld
LinkedIn: shkexcelworld
For full course on Excel
https://www.udemy.com/course/excel-fr...
Learn how you can easily update your results in a Pivot Table report. Download link:
https://drive.google.com/file/d/16sIl...
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/1RugE...
You can also go to my pages and check latest happenings in SHKExcelWorld:
https://www.youtube.com/shkexcelworld...
Facebook: SHKExcelWorld
LinkedIn: shkexcelworld
4 سال پیش
در تاریخ 1399/11/07 منتشر شده
است.
28,201
بـار بازدید شده