Solving FIFO Problem with DAX in Power BI
In this article, I describe how to automate the calculation of cost of sales using the First In, First Out (FIFO) Costing method in Power BI. We discuss what FIFO is, how to set up your model in Power BI, and the DAX measures needed to get the right results.
The Problem: What is FIFO?
The First In, First Out (FIFO) method is an inventory valuation approach where the oldest items in a stock or production queue are used or sold first. In other words, the goods that enter the inventory first or are purchased first, are the first to be utilized or sold, ensuring a chronological order of consumption or distribution. Therefore, when determining the cost of these items as they are sold, the purchase costs of the oldest items are first considered before the more recent costs of purchase, mirroring the natural flow of inventory.
For example, in the image above, we have two purchase batches for Product P1, and a number of sales transactions. The first batch of 20 units costs 2,800 per unit to purchase, and the second batch of 30 units cost 2,750 per unit. Looking at the sales transactions, all sales till 03–05–2021 (DD-MM-YYYY) will have a unit cost of 2,800. However, sales on 04–05–2021 consists of items from both batches. Specifically, 11 units at the cost of 2,800 per unit and 4 units at the cost of 2,750 per unit, which results in a total cost of 41,800. Being able to allocate this unit cost appropriately is what we want to solve in Power BI using DAX
The Algorithm: Logic behind the DAX Calculation
The algorithm behind how we solve this involves simulating the row by row comparison described above in DAX.
First, we generate a list of all the units sold starting from the total sold as at the end of the previous transaction and including all of the current transactions. Next, based on how many units have been sold to date and until the previous date, we are able determine what batch of the purchases, the units sold in the current transaction, are going to be a part of. Once we are able to determine the purchase batch, and consequently, the unit cost for that sale, the aggregation of all these unit cost gives us the total cost of sales for that sales transaction.
The Data Model
The model for this example is a simple one containing a Sales, Purchases, Products and Date table where the Sales & Purchases tables are fact tables and the Products and Date tables are dimension tables.
The content of the Sales and Purchases tables looks like this.
In the Sales table, the Date determines the order of the sales transactions. However, the same product could be sold to different customers for example. To handle such cases, I have added an Index column to break ties on the same day. This helps to identify what Sales order comes first, and which is next. This column could also be a timestamp of when the sales was made.
The Solution
To make the solution simple, we add two additional calculated columns to the Purchases table that tells us the total units that have been purchased as at the current row in the purchases table. The Product RT column computes the running total of units that have been purchased for each product. And the Prev Prod RT computes the running total until the previous purchase.
The code in the Product RT column sums all the purchase units on or before the current row, and the ALLEXCEPT ensures that this running total happens by product and not over all products.
Now, that this is done, we proceed to computing the Cost for each sales transaction. This will be done as a calculated column added to the Sales table.
In the new ‘Cost’ column, we are doing the following: First, we store the current row values for the Product and Index (pseudo date) in variables. Next, we calculate the running total of units sold up to the current row for each product in another variable, and compute the units sold until the previous date.
In the CalcTable variable, we generate a list of numbers that simulates the number of units that have been sold since the previous sales to-date and including the current units sold. Basically, it identifies if this range contains the 10th unit, 11th unit, 12th unit, etc. as in the diagram in the previous section. For each row of this CalcTable, we now determine what the unit cost of that unit sold should be, by filtering the Purchases table to the rows where the unit would fit between the previous purchase to-date and the current purchase to-date. From this filtered table, we extract only the cost column and aggregate all the unit costs extracted to get the total cost of sales for each transaction, and thus our final result.
Conclusion
That was my approach to solving FIFO costing in Power BI using DAX. This calculation technique is applicable to any scenario where FIFO is appropriate as long as a similar data model can be created. However, I have no guarantee on the performance of this approach on a large model. But, as the core of the calculation are calculated columns, the real performance impact would only be at refresh time and not when users are interacting with the report.
I’ll love to hear your experience or thoughts if you try this out. Or if you have an optimized way to do this, do leave a comment below or reach me on LinkedIn or Twitter.
Don’t forget to subscribe to
and join our Power BI community
