Forum Discussion
calculation question
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 ResultTo 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.
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 
How would that work if the column is referencing itself in the ALLEXCEPT part?
The ID is already the main column, thankfully.
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.
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]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.