Substract two rows based on multiple conditions

Hi Anonymous ,

I am assuming you want to substract only two rows (and not more) on the basis of two fields having same value.

You may use below method.

Sample Data:

Steps:

1. Add an Index Column in Power Query.

2. Create another column in DAX to get the Indexing partitoned by first two column like below.

GroupIndex =
CALCULATE(
COUNTROWS('Table (4)'),
FILTER('Table (4)', 'Table (4)'[C1] = EARLIER('Table (4)'[C1])),
FILTER('Table (4)', 'Table (4)'[DateTime] <= EARLIER('Table (4)'[DateTime])),
FILTER('Table (4)', 'Table (4)'[Index] <= EARLIER('Table (4)'[Index]))
)

3. Add another column to find the MOD of index and multiply -1 with it so that it can be use for substraction.

ValueForSubstraction =
VAR vMOD = MOD('Table (4)'[GroupIndex],2)
Return
IF(vMOD = 0, 'Table (4)'[Value] * -1 , 'Table (4)'[Value])

Now you can use this column in report to get the substracted value.

Hope this helps.

Regards,

Hira Negi