cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pers
Frequent Visitor

Share Valuation using FIFO method

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 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 © 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])))

0 REPLIES 0

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors