topic Re: Subtract the previous row with another column with multiple catagory in DAX Commands and Tips

Subtract the previous row with another column with multiple catagory

Anonymous — Thu, 19 Jan 2023 06:10:04 GMT

i need the answer needed like if A1 is one of the catagory material have totally 5 stocks and we needed two so atlast of A1 needed it's 3 like ... D2= C2-B2 ,,, D3= D2-B3...

D4 = C4-B4 ,,, D5=D4 -B5

Help me to find this column...

Re: Subtract the previous row with another column with multiple catagory

v-zhangti — Fri, 20 Jan 2023 02:59:34 GMT

Hi, Anonymous

You can try the following methods. You need to add an index column to Power Query first.

Column:

Cumulative quantity = CALCULATE ( SUM ( 'Table'[quantity] ), FILTER ( 'Table' , [material] = EARLIER ( 'Table'[material] ) && [Index] <= EARLIER ( 'Table'[Index] ) ) )

Result = [stock]-[Cumulative quantity]

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

Re: Subtract the previous row with another column with multiple catagory

Mahesh0016 — Fri, 20 Jan 2023 06:32:37 GMT

Create Calculated Column.
Needed =

VAR Sub =

CALCULATE (

SUM ( 'Subtract Table'[Quantity] ),

FILTER (

'Subtract Table',

'Subtract Table'[Material] = EARLIER ( 'Subtract Table'[Material] )

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

)

RETURN

[Total Stock] - Sub

Anonymous i hope this help you!

Re: Subtract the previous row with another column with multiple catagory

Anonymous — Mon, 23 Jan 2023 15:01:23 GMT

Thank you so much sir...

Re: Subtract the previous row with another column with multiple catagory

Anonymous — Mon, 23 Jan 2023 15:02:45 GMT

Thank you so much sir... It means a lot..