FIFO in Power BI

In this post I would be showing you a solution I came up with that automates the calculation of cost of sales using the First In First Out (FIFO) Costing method. I would be using DAX (Data Analysis Expression) to carry out the calculation. First, I will give a brief description of what FIFO is, the slight complexity of the calculation and the logic I would be implementing before showing any of the DAX code. You would need to have a good understanding of the fundamentals of DAX to make sense of why the code works. Download sample file here.

With multiple sales, numerous purchases and a lot of products, it can get quite cumbersome to calculate.
Can we solve it with DAX? The solution with DAX is quite simple but required some thinking to come up with.
I would explain the logic before writing the DAX formulas.

With that logic in mind we would need to compute the following
For each product…

- How many units have we sold till date

- How many units have we sold till the previous date

- What is the unit cost of those units

- How many units have we purchased till date

- How many units have we purchased till the previous date

With these information we can get our desired solution

This is DAX code we will be writing and I'll walk you through what each part of the code does. It is not complex, it is just long.

To make this work we have to set up the data differently from how the table looks like in the finished solution. We will need (1) a Sales table which would contain the Date, Product, Quantity Sold and the Price or Sales Amount and (2) a Purchases table which would contain information about the Purchase; Date, Product, Quantity Purchased and the Unit Cost (in our example). The sample data files to be used can be downloaded here.

Step 1:
The first thing I did was to add an Index column to the Sales table which identified which sales transaction occurred first and which was after that. And this was done for each product, that is, each product has it own set of running index. Yes, the date provides this same information but you could run into issues when a particular product is sold on the same day at different prices. You could also do this for the Purchases table if this scenario is likely to play out in your purchases as well. There are other ways to achieve this unique identification of the order of the transactions with cleverly written DAX but this was more easy to use for me. You can get this index from Power Query or using any technique you so desire.

Step 2: Add a new column to the Purchases table that computes the how many units you have purchased till date per product. Also add another column that computes the purchases till the previous date.

The first two lines of the code just stores the value of the product and the index in each row of the Sales table in a variable. The 'SalesToDate' computes a running total of the sales for each product up to the currently iterated row in the Sales table. If you return SalesToDate you have the following result.

The next line simply computes the same calculation as the above, running total of sales till the currently iterated index in the sales table. However, we need the sales up to the date (or index) before that. What I did was simply to subtract the current sales from the SalesToDate and that gives me the Sales till the previous date (or index). You can write the formula in a different way but this works just fine.

'CalcTable' is were we begin to implement the logic explained earlier. This were we generate those list of values that are bounded by the SalesToDate (24) and the unit after the sales till the previous date (9+1) like in the logic explanation. If we count the number of rows in this table we created, you would see that we have a number equal to the Quantity Sold.
Note: GENERATESERIES a single-column table named [Value] that contains the series of numbers within the specified boundaries. In our example, this is done for each row of the Sales table.

Now we have the building blocks. A list of values that contains an identifier for when the unit sold was bought. We continue with the logic explained and check for each row of the table from 'CalcTable ' if the [Value] is less than or equal to (<=) to the purchase till date for the given product and also greater than the previous purchase till (which we have calculated as a new column in the Purchases table). This whole process is what is being done in the 'Unit Cost' variable.

The ALLEXCEPT gives the necessary columns which we need from the Purchases table and then FILTER reduces these table to those which are of the current product we are iterating over in the Sales table and where the above logical conditions explained is met. The result would be a single-row table and MINX just gets the value of the Purchases[Unit Cost]. We can't check the content of this variable but what it contains is a table with two columns, one for [Value] and the other for the [@UnitCost].
With this table evaluated, all that is left to do is to iterate over it and sum all the [@UnitCost]. That would be final result which we need. All you need to do is create the necessary measures and you can get the output displayed at the beginning of this post.

There are some assumptions about the nature of the transaction for this code to work. Depending on the peculiarity of of how sales transactions are made, the code would have to be slightly modified. For example, sales (purchase) of the same product at different prices on the same day, sales of half a unit were you can have a sales quantity of say 10.5 units.

Download the .pbix file here.

If you liked the solution or have an optimal solution, I'd love to see it. I know this code can still be greatly improved. Given the fact that it iterates over the fact table, it could be slow on a large dataset. I have not been able to test it on a larger dataset so if you do test it and it performs very well, that would be awesome.

Connect with me on the social platforms below.

Thanks for reading.