FIFO Result Calculation - Does not calculate the result

[pravdon1]

Hi all!

Recently I have found a code sample and file at https://radacad.com/dax-inventory-or-stock-valuation-using-fifo#comment-356322
I have tweaked my data to look the same way as in the sample file and managed the program to show the same results in all columns except the one: FIFO column. Instead of generating "Revenue - COGS" value, it is just generating Revenue number, which does not make sense. Here is my code for "FIFO column" below: 

FIFO column = 
VAR myCurrentSell = 'Table1'[Cumulative Sell Index]
VAR myLastSell = 'Table1'[Previous Cumulative Sell Index]
VAR mySymbol = 'Table1'[KEY]
VAR myCumulativeBuy = 'Table1'[Cumulative Buy Index]
VAR myLastCumulativeBuy = 'Table1'[Previous Cumulative Buy Index]
VAR FIFOFilterTable =
    FILTER (
        'Table1',
        'Table1'[KEY] = mySymbol
            && 'Table1'[Buy/Sell] = "BUY"
            && ( ( 'Table1'[Cumulative Buy Index] >= myLastSell
            && 'Table1'[Cumulative Buy Index] < myCurrentSell )
            || 'Table1'[Cumulative Buy Index] >= myCurrentSell
            && 'Table1'[Previous Cumulative Buy Index] < myCurrentSell
            || 'Table1'[Previous Cumulative Buy Index]  > myLastCumulativeBuy
            && 'Table1'[Cumulative Buy Index] < myLastCumulativeBuy )
    )
VAR FilteredFIFOTable =
    ADDCOLUMNS (
        FIFOFilterTable,
        "New Value", SWITCH (
            TRUE (),
            'Table1'[Cumulative Buy Index] > myLastSell
                && 'Table1'[Previous Cumulative Buy Index] < myLastSell, 'Table1'[Weight (t)] - ( myLastSell - 'Table1'[Previous Cumulative Buy Index] ),
            'Table1'[Cumulative Buy Index] < myCurrentSell, 'Table1'[Weight (t)],
            -- ELSE --
            'Table1'[Weight (t)] - ( 'Table1'[Cumulative Buy Index] - myCurrentSell )
        )
    )
VAR Result =
    Table1[Total value] - SUMX (FilteredFIFOTable, [New Value] * 'Table1'[Price (PLN)])
RETURN
    IF ( 'Table1'[Buy/Sell] = "SELL", Result )

 In the "VAR Result = Table1[Total value] - SUMX ( FilteredFIFOTable, [New Value] * 'Table1'[Cena (PLN)] )", SUMX ( FilteredFIFOTable, [New Value] gives me a blank value no matter what I have tried. Everything works except this piece. Please see attached link for the pbix file:  https://drive.google.com/file/d/1OpQAooD3vJEETTS46HgcwM7wFbeOj5xP/view?usp=sharing   
In Table 2 you will find the original data from RADACAD and in Table 1 you will find my data and work. 

I will be very grateful for helping me solving that last piece of a puzzle!

[pravdon1]

One more question: how do you debug code in Power BI? In Excel VBA you at least have Debug.Print, but here I could not find anything like that. Any tips or tricks to share?))

The reason I am asking is that when I start playing further with my results, I could see flaws in code's logic, where it gave me negative results like this: 
Table 2 negative

Seems like some conditions must be added to the SWITCH statement to eliminate such an anomaly, but for that I need to "evaluate formula" to see what Power BI is seeing. 

@vanessafvg you have replied earlier, but I have edited the reply 😃

[vanessafvg]

depends on what you are trying to debug specifically but typically dax studio is the place

https://daxstudio.org/

https://www.youtube.com/watch?v=gZ4JcqFwJfghttps://www.youtube.com/watch?v=fV2ZK4q3FBQ

https://www.youtube.com/watch?v=fV2ZK4q3FBQ

[pravdon1 · Accepted solution]

@vanessafvg YES, YOU ARE RIGHT! I have completely missed that logic! My KEY column must be the same all the time!

Basically I used 'Data groups' to create a separate column and named it "GLOWKA", so it gives me only one product (in my business I only care about the quantities in & out). 

Solution

Thanks for pointing that out, Vanessa! I have been quite myopic 😃 

[vanessafvg]

happens to the best of us!

[pravdon1]

@vanessafvg thanks for your prompt reply! Unfortunately, that did not solve it. Let me elaborate using snippets for a better understanding. 

In Table 2 if you look closely we can see "FIFO column COGS only" and "FIFO column COGS Units only" have displayed data in columns, but in Table 1 it is just BLANK! 

Table 2 - good!

Table 1 - not good

Here is a sample table:

Date	Key	Buy/Sell	Weight	Price	Total Value
30.03.2021	GLOWKA	BUY	0.78	1650	1287
06.04.2021	GLOWKA	BUY	19.92	1650	32868
10.04.2021	GLOWKA	BUY	19.94	1650	32901
11.05.2021	KULIA	SELL	25.24	2665	67265

The FIFO calculation logic looks like this: 

67,265 - ((0.78 x 1650) + (19.92 x 1650) + (4.54 x 1650)) = 25,619 <-- this is the result the Power BI must spit out, but unfortunately I only see 67,265 in the "FIFO column".

Please download the slightly updated pbix file from the link given above to reflect the screenshots.

P.S.: Just to note: this part "((0.78 x 1650) + (19.92 x 1650) + (4.54 x 1650))" is equivalent to "SUMX (FilteredFIFOTable, [New Value] * Table1[Price (PLN)])"

[vanessafvg]

yep its quite a complicated one to work through  and I can't say for sure, as you would need to try and fix this in your data but you aren't getting values back because I think your keys either buy or sell not both, whereas the code provided that you have changed, and the data provided, all they keys / symbols  have both buy and sell values.

hope that makes sense.  Since you are using the key to filter and providing both buy and sell as conditions in the measures, you will probably never get a record back that meets both those constraints.

if you see here all of these except CCC have buy and sell values

and if you look at the values returned for c there aren't any

[vanessafvg]

it looks to me like you dont have the aggregation set on for your fifo column, change to sum