cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## How to sum value in column with a condition in other column

Hi,
I'm having trouble calculating the sum of one column based on the condition of another column.
My dataset (an example below) contains rows that represent inventory movements, movements can be of three types:
In the case of a setup type row it represents an inventory type row, therefore an existence at a precise date, with this condition I have to calculate the stock starting from the last setup type row onwards.

 Product Code Date (DD/MM/YYYY) Type Qty 001 01/01/2021 setup 20 001 02/01/2021 load 10 001 03/01/2021 unload -15 001 04/01/2021 setup 14 001 06/01/2021 unload -3

the final quantity of product 001 will be 11.

could you show me a solution to calculate this sum?

Thank you for the help

Resolver II

Hi daviddalterio90,

Let's say your table is CountQty, then you can use below code to count Qty since the last "setup"

FinalStock =

// To find the last Date when the type is "setup"
VAR LastSetupDate =
CALCULATE(
LASTNONBLANK( CountQty[CodeDate], MAX( CountQty[Qty] ) ),
CountQty[Type] = "setup"
)

// To filter the table where the CodeDate is bigger than LastSetupDate

VAR FilteredTable =
CALCULATETABLE( CountQty, CountQty[CodeDate] >= LastSetupDate )

// Sum all Qty from the filtered table

VAR Result =
SUMX( FilteredTable, CountQty[Qty] )

RETURN
Result

Hope this helps you. Thanks