Share Valuation using FIFO method

[PRA]

Hello - I am trying to implement FIFO in share valuation. I was able to get it in excel, but not able to get the calculation in Power BI.
I have gone through the link on inventory - but could not utilize it for lack of clarity.

Inventory Management [Shortage report]

Hi Guys Is it possIble to make an inventory shortage report based on a data model with 3 tables BOM items transactions. [ item Id, transaction date, Warehouse code, required quantity ] Inventory Onhand snap shot [ Item Id, Onhand quantity per warehouse, Warehouse code] Date table [Date, ISO week number, Month, Year, etc.] Allocate onhand inventory for each BOM Item by FIFO principle I would like to have an overview, when a BOM item have a shortage, based on transactions date and Onhand inve…

I am including the sample data (input and output) as was done in excel - gdrive link (https://drive.google.com/open?id=1vsc8R5MnGy2MVmqxUI7wgHssl6K6wjgM). The blue header columns in FINAL RESULT are the required output - I need help in creating these columns in Power BI - (a) CAPITAL GAIN (b) STOCK IN HAND (c) CLOSING VALUE

Also is the link for the PBX file (https://drive.google.com/open?id=1irJ2vOyV50WdVyYU45YQwA12rkqUNojm), where I am not able to fix the Calculated Column - “Cost Basis with FIFO”. Below is the DAX calculation for FIFO method that I am using - which is not working as per FIFO as it is not picking RATE (the multiplication factor) of the BUY QTY - rather it is picking the current row (SALE RATE), which is not the expected behavior of FIFO.

Cost Basis with FIFO = 
VAR myUnits=[Qty.]
VAR PreviousBuys= 
    FILTER(TransactionData,
           [IsIn]=EARLIER([IsIn])&&
           [Sauda Date]<EARLIER([Sauda Date])&&
           ([Net Qty.] > 0)
          )
VAR PreviousSales=
    SUMX(
        FILTER(TransactionData,
            [IsIn]=EARLIER([IsIn])&&
            [Sauda Date]<EARLIER([Sauda Date])&&
            ([Net Qty.] < 0)
             ),
        [Qty.]
        )
VAR PreviousBuysBalance=
    ADDCOLUMNS(
        ADDCOLUMNS(
            PreviousBuys,
            "Cumulative", 
                SUMX(
                    FILTER(PreviousBuys,
                        ([Sauda Date]<=EARLIER([Sauda Date]))
                          ),
                    [Qty.])
                  ),
        "Balance Left",
        [Qty.]-IF([Cumulative]<PreviousSales,
        [Qty.],
        VAR PreviousCumulative=[Cumulative]-[Qty.] 
  RETURN 
    IF(PreviousSales>PreviousCumulative,PreviousSales-PreviousCumulative)
                )
            )
VAR CostUsed=
    ADDCOLUMNS
    (
        ADDCOLUMNS
        (
            PreviousBuysBalance,
            "MyCumulatives",
                SUMX
                (
                    FILTER(PreviousBuysBalance,
                          ([Sauda Date]<=EARLIER([Sauda Date]))
                          ),
                [Balance Left])
        ),"Balance Used",
            IF(
                [MyCumulatives]<myUnits,[MyCumulatives],
                VAR PreviousCumulatives=[MyCumulatives]-[Balance Left]
        RETURN
            IF(myUnits>PreviousCumulatives,myUnits-PreviousCumulatives)
            )
    )
RETURN
  IF([Net Qty.] < 0,
  ([Qty.]*[Rate])-SUMX(CostUsed,([Balance Used]*[Rate])))

FIFO_SampleInput.xlsx (849.2 KB)

FIFO_SampleInput.pbix (378.7 KB)

[Melissa]

@PRA

You should check out this article: DAX Inventory or stock valuation using FIFO by Philip Seamark.

That worked for me, in my case I had to work of an entry number and not a posting date because we could have multiple transactions on a single day for an item.

[PRA]

@Melissa - Thanks for the reference. I did go through this earlier; my data set is similar to yours - multiple products; many buy and many sell on same date.
The article mentions this, but does not clarify on what is the “other column” to be used, or am I missing something? Any direction will help.

“If your dataset has more than 1 per day, a different column should be used to order the cumulative total.”

[Melissa]

@PRA,

You need an unique value, so if you have multiple transactions on the same day it can’t be the date column. As I mentioned earlier for me it was the “entry number” that’s like an index number for each individual transaction and keeps every single posting thats done in right order. If you have date and time for each transaction you could sort your data in Power Query and add an Index number there.

Hope this helps.

[PRA]

@Melissa- Thanks for the direction; I agree a unique identifier is required for each row, to capture lowest level of granularity. I did introduce an Index column and also Buy Sequence column, but not able to get FIFO for rate value. Could I request your view on the annexed PBIX, maybe I am not rightly using the index, though I continue to test multiple permutations. Thanks.

[Melissa]

Can you supply a sample in PBIX of your latest file version?

[PRA]

@Melissa

Kindly find annexed the PBIX and the sample dataset.
In the Sample Dataset, the worksheet BUY and SELL are original transaction format. The worksheet “FINAL RESULT” was created by appending data of BUY and SELL; and then sort on “Sauda Date” column; and final outcome columns are below, that I am trying to replicate in PBIX:

1. Cum Buy Qty (comment: created in PBIX)
2. Cum Buy Cost (comment: created in PBIX)
3. Cum Sell Qty (comment: created in PBIX)
4. Cum Sell Cost (comment: created in PBIX)
5. Capital Gain
6. Stock In Hand
7. Closing Value

Kindly refer to Query (Table) - TransactionData; Column Names - “FIFO”; “Index”; “BuySeq” (not “Buy Seq”…this has space in its name and created at Query level; while other is Calculated Column that is to be used)

FIFO_SampleInput.xlsx (849.2 KB)
FIFO_SampleInput.pbix (371.7 KB)

[Melissa]

Hi PRA,

A few things I’ve noticed about your data and model:

1. There is a relationship between the table Buy and TransactionData on your [Buy Seq] field - it may be autodetected, so check “Options and Settings” => Options => Current file => Data Load and deselect “Autodetect new relationschips after data is loaded”. And always check the relationships in your model because I don’t believe you need this relationship.
2. There is no date table in your model this is required for most time intelligence calculations, so it’s best practice to always add a date table if you have a date field anywhere in your model.
3. When I sorted [Sauda Date] your [Index] number was no longer in chronological order. Since this would be the basis for your FIFO calculation any result would subsequently be wrong.

Best regards,
Melissa

[Melissa]

@PRA

I made a copy of your TransactionData and added a simple Date table as well, see the NewModel below.

Then added these calculated columns to your fact table:

Direction = 
    IF( 'TransactionData Clean'[Net Qty.] <0 | "Out" | "In" )


FIFO Remaining = 
VAR myItem = 'TransactionData Clean'[IsIn]
VAR myRecord = 'TransactionData Clean'[Index]
VAR lastRecord = MAX('TransactionData Clean'[Index] )

VAR TotalIN = 
    CALCULATE( 
        SUM( 'TransactionData Clean'[Net Qty.] )|
        FILTER( 'TransactionData Clean' |
            'TransactionData Clean'[IsIn] = myItem &&
            'TransactionData Clean'[Index] <= myRecord &&
            'TransactionData Clean'[Direction] = "In"
        )
    )

VAR TotalOUT =
    CALCULATE( 
        SUM( 'TransactionData Clean'[Net Qty.] )|
        FILTER( 'TransactionData Clean' |
            'TransactionData Clean'[IsIn] = myItem &&
            'TransactionData Clean'[Index] <= lastRecord &&
            'TransactionData Clean'[Direction] = "Out"
        )
    )

VAR Result = 
    IF( ISBLANK( TotalIN ) | BLANK() |
        IF( -TotalOUT > TotalIN | 0 | TotalIN + TotalOUT 
        )
    )
RETURN

    IF( 'TransactionData Clean'[Direction] = "In" | 
        Result
    )


Previous IndexNo = 
VAR myItem= 'TransactionData Clean'[IsIn]
VAR myRecord = 'TransactionData Clean'[Index]
VAR myFilterTable =    
         FILTER(
            'TransactionData Clean' |
            'TransactionData Clean'[IsIn] = myItem &&
            'TransactionData Clean'[Index] < myRecord &&
            'TransactionData Clean'[Direction] = "In"
        )
VAR LastIn = 
    MAXX( 
        myFilterTable |
        'TransactionData Clean'[Index]
    )
RETURN 

    IF( 'TransactionData Clean'[Direction] = "In" |
        LastIn 
    )

NOTE.
If you fix the [Index] so when [Sauda Date] is sorted your [Index] number is still in chronological order, this FIFO calculation should work.

FIFO Remaining QTY = 
VAR myTable =
ADDCOLUMNS(    
    ADDCOLUMNS(
        CALCULATETABLE( 'TransactionData Clean' |
            'TransactionData Clean'[FIFO Remaining] >0 |
            USERELATIONSHIP( 'TransactionData Clean'[Sauda Date] | 'DATE'[Date] ))|
        "PreviousQTY" | IF( NOT( ISBLANK( 'TransactionData Clean'[Previous IndexNo] ))|
            LOOKUPVALUE( 'TransactionData Clean'[FIFO Remaining]| 'TransactionData Clean'[Index] | 'TransactionData Clean'[Previous IndexNo] )|
            0 )|
        "UnitPrice" |  DIVIDE( 'TransactionData Clean'[Amount] | 'TransactionData Clean'[Net Qty.] | 0 ))|
    "RemQTY" | 'TransactionData Clean'[FIFO Remaining] - [PreviousQTY] |
    "CostPriceValue" | ('TransactionData Clean'[FIFO Remaining] - [PreviousQTY]) * [UnitPrice] )
RETURN

SUMX( myTable | [RemQTY] )

And to get the remaining Value just change SUMX in this Measure to:

SUMX( myTable | [CostPriceValue] )

Here is your PBIX file back with my changes, I also added a FileLocation parameter so you can easely change that to point to your XLSX file. FIFO_SampleInput v2.pbix (428.4 KB)

Best of luck.

[PRA]

@Melissa, thank you for the suggestion. I will try out and test. I am aware of “Index” column not in sync with “Sauda Date” column and I have trying to fix it for a while now. I will implement your suggestions and see if this does the trick. Appreciate your guidance, will revert.
best regards