DAX - Create calculated column

Hi, Troyes 

You can click ‘Edit Queries’ to enter into the Query Editor, go to  ‘Add Column’ -> click ‘Index Column’ to add index column (start from 0).

Then, you can create a calculated column as follows:

New QTY =

VAR t =

FILTER (

'Table',

[Product] = EARLIER ( 'Table'[Product] )

&& [Index] < EARLIER ( 'Table'[Index] )

)

VAR totalDiff =

CALCULATE ( SUM ( 'Table'[DIFF QTY] ), t )+[DIFF QTY]

VAR minleft =

CALCULATE ( SUM ( 'Table'[Usage QTY] ), t )

RETURN

IF (

minleft > totalDiff,

0,

IF (

[DIFF QTY] <> 0,

[Usage QTY],

IF (

minleft = 0,

0,

IF ( minleft + [Usage QTY] > totalDiff, totalDiff - minleft, [Usage QTY] )

)

)

)

Result:

If I misunderstand the your thoughts, please inform me of your expected output. I am glad to solve the problem for you.

Best Regards,

Allan

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