Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |