Substract two rows based on multiple conditions

[Anonymous]

Hello,

I need some help to substract two rows based on two conditons on Power BI. 

The first one is the name of the elctric meters ans the second the day and hour of meter readings.

Thank you for your help.

Best regards

[HiraNegi]

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

[ryan_mayu]

@Anonymous 

is this based on line order, not the time?

could you pls the sample data and expected output?