DAX - Create calculated column

[Troyes]

Hi,

I'm trying to create a new column [New Qty] using DAX which convert the [Diff Qty] to the max values of [Usage Qty] in the same row and extend the [New Qty] to the next row until the sum equals the [Diff Qty], based on Product ID. Could anyone help me on that? Thanks.

[v-alq-msft · Accepted solution]

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.

[Troyes]

Hi @v-alq-msft 

Thanks for your reply. It works fine for me.