Forum Discussion
DAX - Create calculated column
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.
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.
2 Replies
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.
