calculation question

[zahlenschubser]

I have a rather large table with inventory movements, with changing purchase prices over time, and I need to calculate the difference between the then-current price and the last previous one.

I tried using the following formula, but for whatever reason I keep getting the same difference amount for later entries, and I cannot figure out what's wrong.

The first line is there to make sure the first line per item shows as zero, as that didn't work properly either.

diff_amount =

var MINEK   = X_Lagerwerte[Id] = minx(filter(X_Lagerwerte

                                            , X_Lagerwerte[Artikel_id] = EARLIER(X_Lagerwerte[Artikel_id])

                                            )

                                , X_Lagerwerte[Id])

var LC      = round(X_Lagerwerte[EK_LC] , 2)

var PLC     = ROUND(calculate(LASTNONBLANK(X_Lagerwerte[EK_LC] , 0)

                                , FILTER(X_Lagerwerte

                                            , X_Lagerwerte[Artikel_id] = EARLIER(X_Lagerwerte[Artikel_id])

                                            && X_Lagerwerte[Id] < EARLIER(X_Lagerwerte[Id])

                                        )

                    ) , 4 )

var calc    = LC - PLC

return      switch(TRUE()

                    , MINEK

                        , 0

                    , calc = 0 || ISBLANK(X_Lagerwerte[EK_LC]) || calc = round(X_Lagerwerte[EK_LC] , 4)

                        , 0

                    , calc

                    )

First column is the database ID counter for the movement, then the movement date and the item ID and number.
Ignore the x columns, just for spacing because the forum is being weird with the table.

Id	x	Datum	x	Artikel_id	Artikel_nummer	EK_LC	x	diff_amount
714617		15.12.2023		367	4020	1,5500		0
842002		21.08.2024		367	4020	0,5995		-0,95
892122		27.11.2024		367	4020	0,5995		-0,95
892632		28.11.2024		367	4020	0,5995		-0,95
916045		13.01.2025		367	4020	0,5995		-0,95
927772		04.02.2025		367	4020	0,5995		-0,95
939514		21.02.2025		367	4020	0,5995		-0,95

[johnt75 · Accepted solution]

In the model view select the table and set the key column to Id. Then you can create a calculated column like

Diff Amount = 
VAR CurrentAmount = X_Lagerwerte[EK_LC]
VAR PrevAmount = SELECTCOLUMNS( 
    OFFSET( -1, ALLEXCEPT( X_Lagerwerte, X_Lagerwerte[Diff Amount] ),
        ORDERBY( X_Lagerwerte[Datum], ASC, X_Lagerwerte[Id], ASC ),
        PARTITIONBY( X_Lagerwerte[Artikel_id], X_Lagerwerte[Artikel_nummer] )
    ),
    X_Lagerwerte[EK_LC]
)
VAR Result = IF( ISBLANK( PrevAmount ), 0, CurrentAmount - PrevAmount )
RETURN Result 

[johnt75 · Accepted solution]

In the model view select the table and set the key column to Id. Then you can create a calculated column like

Diff Amount = 
VAR CurrentAmount = X_Lagerwerte[EK_LC]
VAR PrevAmount = SELECTCOLUMNS( 
    OFFSET( -1, ALLEXCEPT( X_Lagerwerte, X_Lagerwerte[Diff Amount] ),
        ORDERBY( X_Lagerwerte[Datum], ASC, X_Lagerwerte[Id], ASC ),
        PARTITIONBY( X_Lagerwerte[Artikel_id], X_Lagerwerte[Artikel_nummer] )
    ),
    X_Lagerwerte[EK_LC]
)
VAR Result = IF( ISBLANK( PrevAmount ), 0, CurrentAmount - PrevAmount )
RETURN Result 

[zahlenschubser]

How would that work if the column is referencing itself in the ALLEXCEPT part?

The ID is already the main column, thankfully.

[johnt75]

That's to prevent a circular dependency error. It looks like an error in the formula bar, but that's just an Intellisense issue, the column does actually work OK.

[zahlenschubser]

Thank you very much, that did help!
I didn't fully understand what you did there, could you elaborate a little?

Also I have another column with a percentage change, and that gives me a circular dependency error now which I don't understand why?

diff_percent = (X_Lagerwerte[EK_LC] - X_Lagerwerte[Diff Amount]) / X_Lagerwerte[Diff Amount]

[johnt75 · Accepted solution]

To avoid the circular dependency error in your % calculation you will need to add that column to the ALLEXCEPT in the diff amount column.

The code I wrote works using the OFFSET function. That sorts the table specified by date and ID ( the ID is in case there are multiple entries for one date ) and partitions it by the article ID and number. It then chooses the previous row relative to the current row, and pulls the amount from that previous row.

The reason that you need to include any calculated columns in the ALLEXCEPT is that otherwise OFFSET would work on the entire table, which would include those calculated columns.

I hope this explains how it works.

[pankajnamekar25]

Hellio @zahlenschubser 

You this mesure

diff_amount =

VAR CurrentId = X_Lagerwerte[Id]

VAR ArtikelId = X_Lagerwerte[Artikel_id]

VAR CurrentEK = X_Lagerwerte[EK_LC]

VAR PrevEK =

    CALCULATE(

        MAX(X_Lagerwerte[EK_LC]),

        TOPN(

            1,

            FILTER(

                X_Lagerwerte,

                X_Lagerwerte[Artikel_id] = ArtikelId &&

                X_Lagerwerte[Id] < CurrentId

            ),

            X_Lagerwerte[Id],

            DESC

        )

    )

VAR IsFirstEntry =

    CurrentId = CALCULATE(

        MIN(X_Lagerwerte[Id]),

        FILTER(

            X_Lagerwerte,

            X_Lagerwerte[Artikel_id] = ArtikelId

        )

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[zahlenschubser]

That contains only variables, did you pull this from chatgpt?