Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |