Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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:
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:
Hi @StockTrader ,
please check this article:
DAX : Inventory or Stock Valuation using FIFO - RADACAD
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks. Your solution is FIFO. I need help to get LIFO working. Thanks though.