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.
Hi, I need to create a margin calculation for stock using the FIFO way.
I have found some example, but it's giving me a negative FIFO calculation.
https://radacad.com/dax-inventory-or-stock-valuation-using-fifo
I have spent already a week trying to solve this and have no idea where is the problem.
If somebody likes solving a puzzle and would give me some help I would be appreciated.
Thanks.
Here is the FIFO column calculation
FIFO column =
VAR myCurrentSell = 'Table1'[Cumulative Sell]
VAR myLastSell = 'Table1'[Previous Cumulative Sell]
VAR mySymbol = 'Table1'[Symbol]
VAR myCumulativeBuy = 'Table1'[Cumulative Buy]
VAR myLastCumulativeBuy = 'Table1'[Previous Cumulative Buy]
VAR FIFOFilterTable =
FILTER (
'Table1',
'Table1'[Symbol] = mySymbol
&& 'Table1'[type] = "Buy"
&& ( ( 'Table1'[Cumulative Buy] >= myLastSell
&& 'Table1'[Cumulative Buy] < myCurrentSell )
|| 'Table1'[Cumulative Buy] >= myCurrentSell
&& 'Table1'[Previous Cumulative Buy] < myCurrentSell
|| 'Table1'[Previous Cumulative Buy] > myLastCumulativeBuy
&& 'Table1'[Cumulative Buy] < myLastCumulativeBuy )
)
VAR FilteredFIFOTable =
ADDCOLUMNS (
FIFOFilterTable,
"New Value", SWITCH (
TRUE (),
'Table1'[Cumulative Buy] > myLastSell
&& 'Table1'[Previous Cumulative Buy] < myLastSell, 'Table1'[Units]
- ( myLastSell - 'Table1'[Previous Cumulative Buy] ),
'Table1'[Cumulative Buy] < myCurrentSell, 'Table1'[Units],
-- ELSE --
'Table1'[Units]
- ( 'Table1'[Cumulative Buy] - myCurrentSell )
)
)
VAR Result =
Table1[Total value] - SUMX ( FilteredFIFOTable, [New Value] * 'Table1'[value per unit] )
RETURN
IF ( 'Table1'[type] = "Sale", Result )
and in the attachment is the screenshot with negative value;
All is good for the "AAA" item, the problem is with "mmm" one.
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.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |