Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need Help with Cumulative difference based on item#

Hi,   I am reaching out to this group for help in calculating cumulative difference of quantity for an item. and the calculation for first instance of the item will be base calculation where as the...
Need Help
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, Anonymous 

 

You may create a calculated column and a measure as below. The pbix is attached in the end.

FormattedMonthYear = VALUE(FORMAT(Sheet1[Month Year],"yyyymm"))
Qty Remaining = 
var _base =
CALCULATE(
    AVERAGE(Sheet1[Curr Released Inv Qty (Base qty)]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Item #] = SELECTEDVALUE(Sheet1[Item #])
    )
)
var _required = 
CALCULATE(
    SUM(Sheet1[Required ( same as Total Fqty)]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Item #] = SELECTEDVALUE(Sheet1[Item #])&&
        Sheet1[FormattedMonthYear]<=SELECTEDVALUE(Sheet1[FormattedMonthYear])
    )
)
return
_base-_required

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.