cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
daviddalterio90
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:
load, unload, setup.
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 CodeDate (DD/MM/YYYY)TypeQty
00101/01/2021setup20
00102/01/2021load10
00103/01/2021unload-15
00104/01/2021setup14
00106/01/2021unload-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

 

1 REPLY 1
colacan
Resolver II
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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.