Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a FIFO cost of goods sold calculation from information i gleaned over the internet. I am having an issue with the code. If I define the "units" as a Int64.Type, it creates a whole number and the cost of goods sold is calculated, albeit the wrong amount due to the truncation of the amount of units.
If I define "units" as a decimal type, the cost of goods sold calculated is null. Hoping someone can help me out. Here is the code.
let
Source = TRANSACTIONS,
#"Renamed Columns" = Table.RenameColumns(Source,{{"aType", "Type"}, {"SECURITY", "Symbol"}, {"SHARES", "Units"}, {"PRICE", "Value per Unit"}, {"TOTAL VALUE", "Total Value"}}),
SortDateID = Table.Sort(#"Renamed Columns", {{"DATE", Order.Ascending}}),
RemoveErrors = Table.ReplaceErrorValues(SortDateID, {{"Units", 0}, {"Value per Unit", 0}}),
ReplaceNulls = Table.ReplaceValue(RemoveErrors, null, 0, Replacer.ReplaceValue, {"Units", "Value per Unit"}),
RemoveCol = Table.RemoveColumns(ReplaceNulls,{"Total Value", "Index"}),
// ReplaceNaN = Table.ReplaceValue(#"Removed Columns", #nan, 0, (value, old, new) => if Number.IsNaN(value) then new else value, {"Units", "Value per Unit"}),
ChColType = Table.TransformColumnTypes(RemoveCol, {{"Units", type number}, {"Value per Unit", type number}}),
AddTotalValue = Table.AddColumn(ChColType, "Total Value", each [Value per Unit] * [Units], type number),
GroupRows = Table.Group( AddTotalValue, {"Symbol"},
{
{"AllRows", each
let
t= Table.AddIndexColumn( Table.Sort( _,{{"Type", Order.Ascending}, {"DATE", Order.Ascending}}), "Index", 0, 1 ),
BuysOnly = Table.SelectRows( t, each ([Type] = "B")),
AddAbsUnits = Table.AddColumn( BuysOnly, "Abs Units", each Number.Abs([Units]), type number),
AddAbsValue = Table.AddColumn( AddAbsUnits, "Abs Total", each Number.Abs([Total Value]), type number),
GroupBuys = Table.Group( AddAbsValue, {"Symbol", "Type", "Abs Units", "Abs Total"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Sum Units", each List.Sum([Units]), type number},
{"AllRows2", each
let
buys = Table.Sort( _, {{"Units", Order.Ascending}, {"Index", Order.Ascending}}),
index2 = Table.AddIndexColumn( buys, "n", 1, 1, Int64.Type),
running = Table.AddColumn( index2, "RT", each List.Sum( List.FirstN( index2[Units], [n] )), type number),
result = Table.SelectRows( running, each [RT] >0,type number )[[Units] , [Value per Unit]]
in
result, type table [Units, Value per Unit]
}
}),
NoNulls = Table.SelectRows( GroupBuys, each ([Sum Units] <> 0), type number)[[AllRows2]],
ExpandResult = Table.ExpandTableColumn( NoNulls, "AllRows2", {"Units", "Value per Unit"}, {"Units", "Value per Unit"}),
lCost = List.Buffer( List.Combine( Table.AddColumn( ExpandResult, "Value", each List.Repeat( {[Value per Unit]}, [Units]))[Value])),
AddRecord = Table.AddColumn( t, "Values", each
[
i = [Index],
u = [Units],
CumPrevSell = List.Sum( Table.SelectRows( t[[Type], [Units], [Index]], each ([Type] = "S" and [Index] < i))[Units] ),
Sold Cost = let pSell = if CumPrevSell = null or CumPrevSell-1 < 0 then 0 else CumPrevSell in
try
if [Type] = "S" then List.Sum( List.FirstN( List.Skip( lCost, pSell ), u, type number )) else null
otherwise null
]
)
in
AddRecord, type table [DATE=nullable number, Symbol=nullable number, Units=nullable number, Value per Unit=nullable number, Type=nullable text, Total Value=nullable number, Index=nullable number]
}
} )[[AllRows]],
ExpandAllRows = Table.ExpandTableColumn(GroupRows, "AllRows", {"DATE", "Symbol", "Units", "Value per Unit", "Type", "Total Value", "Index", "Values"}, {"DATE", "Sybol", "Units", "Value per Unit", "Type", "Total Value", "Index", "Values"}),
GetCost = Table.ExpandRecordColumn(ExpandAllRows, "Values", {"Sold Cost"}, {"Sold Cost"})
in
GetCost
Thanks for any insight.
Randymone
Hi @randymone
Maybe the data coming from the source has a different locale that what your PBIX has? For example, decimal seperators from the source are . and your pbix is , this in my expierence causes issues
If this is the case, highlight all columns with a value in it and replace the . with , or change the locale in your PBIX
Hope fully this helps
Joe
If this post helps, then please Accept it as the solution
Thanks for the response.
This is a personal project and I am not using PowerBI, only Power Query. The regional settings for the current workbook indicate the United States. Transactions, the source of the data, is another Power Query which transforms the uploaded raw data. The columns were renamed in #"Renamed Columns" to be consistent with the the code i found on the internet.
In debugging a bit further, it appears the issue is with the code:
try
if [Type] = "S" then List.Sum( List.FirstN( List.Skip(lCost, pSell), u)) else null
otherwise null
which appears to create the null for the Cost Sold. (I changed it to otherwise "xxx" which was the result for a sale transaction.)
I am not sophisticated enough to parse the code to be able to see what happens in the individual steps (and see what the values are being used for lcost, psell, and U.) to troubleshoot the issue.
Wondering whether the List.Skip function would ever provide a result if the data types being used are inconsistent.
Any insight would be appreciated.
Randymone
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.