Forum Discussion

StockTrader's avatar
StockTrader
Regular Visitor
3 years ago
Solved

Need Help with LIFO Stock Profit/Loss Calculation

Hi, I am new to DAX and need some help calculating LIFO-based method for stock trade P&L.

 

I'd appreciate any hints to fix the code. Few things to point out:

 

1) I am relying on Table2 for LIFO. Table1 uses FIFO. Refer to the file attached.

2) The first two sales for TSLA are correct ($1,800 and -$1,450).

3) The last two sales should've been $1,400 and $4,500 respectively (following LIFO method). However, I am getting -$250 and -$450.

 

 

Cost Basis with LIFO calc = VAR myunits=[Units]
VAR Previous_buys=Filter(Table2,[Symbol]=earlier([Symbol])&&[date]<=earlier([date])&&[type]="buy")
VAR Previous_sales=SUMX(Filter(Table2,[Symbol]=earlier([Symbol])&&[date]<earlier([date])&&[type]="sale"),[Units])
VAR Previous_buys_balance=ADDCOLUMNS(ADDCOLUMNS(Previous_buys,"Cumulative",SUMX(Filter(Previous_buys,[date]<=earlier([date])),[Units])),"Balance Left",[Units]-if([Cumulative]<Previous_sales,[Units],VAR previouscumulative=[Cumulative]-[Units] RETURN IF(Previous_sales>previouscumulative,Previous_sales-previouscumulative)))
VAR CostUsed=ADDCOLUMNS(ADDCOLUMNS(Previous_buys_Balance,"MyCumulatives",SUMX(Filter(Previous_buys_balance,[date]>earlier([date])),[Balance Left])),"Balance Used",IF( 
			[Balance Left] + [MyCumulatives] <= myunits,
			[Balance Left],
			IF ( myunits >= [MyCumulatives], myunits -[MyCumulatives])
		))
RETURN
if([type]="sale",[Units]*[value per unit]-SUMX(CostUsed,[Balance Used]*[value per unit]))

 

 

The link to the file is here for reference. Thanks in advance.

Need Help
  • StockTrader's avatar
    StockTrader
    3 years ago

    In case someone else needs this, here is the solution:

     

    Add one column, LIFOTEMP:

     

    LIFOTemp = 
VAR Previous_buys=Filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<=earlier([Transaction_Date])&&[Order_Action]="buy")
VAR Previous_sales=SUMX(Filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<=earlier([Transaction_Date])&&[Order_Action]="sale"),[Quantity])
var x=SUMX(Filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<=earlier([Transaction_Date])&&[Order_Action]="buy"),[Quantity])-Previous_sales
VAR Previous_buys_balance=ADDCOLUMNS(ADDCOLUMNS(ADDCOLUMNS(Previous_buys,"Cumulative",SUMX(Filter(Previous_buys,[Transaction_Date]<=earlier([Transaction_Date])),[Quantity])),"Balance Left",IF([Cumulative]-x<=0,0,IF([Cumulative]-x>Table2[Quantity],Table2[Quantity],[Cumulative]-x))),"valu",[Balance Left]*Table2[value per unit])
var nn=SUMX(Filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<=earlier([Transaction_Date])&&[Order_Action]="sale"),[Quantity]*Table2[value per unit])
var re=IF(Table2[Order_Action]="sale", nn-SUMX(Previous_buys_balance,[valu]))
return re

    Then this column would have the calculation:

    Cost Basis with LIFO calc = var z=filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<earlier([Transaction_Date])&&[Order_Action]="sale")
var zzzz=MAXX(z,'Table2'[Transaction_Date])
var ddd=MAXX(FILTER(z,'Table2'[Transaction_Date]=zzzz),Table2[LIFOTemp])
var re=IF(Table2[Order_Action]="sale", Table2[LIFOTemp]-ddd)
return re

     

    This is what the table would look like:

     

     

3 Replies

  • StockTrader's avatar
    StockTrader
    Regular Visitor
    3 years ago

    In case someone else needs this, here is the solution:

     

    Add one column, LIFOTEMP:

     

    LIFOTemp = 
VAR Previous_buys=Filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<=earlier([Transaction_Date])&&[Order_Action]="buy")
VAR Previous_sales=SUMX(Filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<=earlier([Transaction_Date])&&[Order_Action]="sale"),[Quantity])
var x=SUMX(Filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<=earlier([Transaction_Date])&&[Order_Action]="buy"),[Quantity])-Previous_sales
VAR Previous_buys_balance=ADDCOLUMNS(ADDCOLUMNS(ADDCOLUMNS(Previous_buys,"Cumulative",SUMX(Filter(Previous_buys,[Transaction_Date]<=earlier([Transaction_Date])),[Quantity])),"Balance Left",IF([Cumulative]-x<=0,0,IF([Cumulative]-x>Table2[Quantity],Table2[Quantity],[Cumulative]-x))),"valu",[Balance Left]*Table2[value per unit])
var nn=SUMX(Filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<=earlier([Transaction_Date])&&[Order_Action]="sale"),[Quantity]*Table2[value per unit])
var re=IF(Table2[Order_Action]="sale", nn-SUMX(Previous_buys_balance,[valu]))
return re

    Then this column would have the calculation:

    Cost Basis with LIFO calc = var z=filter(Table2,[Ticker]=earlier([Ticker])&&[Transaction_Date]<earlier([Transaction_Date])&&[Order_Action]="sale")
var zzzz=MAXX(z,'Table2'[Transaction_Date])
var ddd=MAXX(FILTER(z,'Table2'[Transaction_Date]=zzzz),Table2[LIFOTemp])
var re=IF(Table2[Order_Action]="sale", Table2[LIFOTemp]-ddd)
return re

     

    This is what the table would look like:

     

     

    • StockTrader's avatar
      StockTrader
      Regular Visitor
      3 years ago

      Thanks. Your solution is FIFO. I need help to get LIFO working. Thanks though.