Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
@Anonymous
is this based on line order, not the time?
could you pls the sample data and expected output?
Proud to be a Super User!
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |