Forum Discussion
Subtract rows in a Table based on a id
Hi Chirantan,
To achieve your requirement, you can create a calculate column as below:
Calculation = VAR First_Value = Table1[Value] VAR First_Index = Table1[Index] VAR Last_Value = CALCULATE(MAX(Table1[Value]), FILTER(Table1, Table1[Index] = First_Index + 4)) RETURN IF(Last_Value = 0, 0, Last_Value - First_Value)
Regards,
Jimmy Tao
Community ChampionSo, where is the first number in your Calculation column coming from? I mean, what are the rules?
But, in general, you are probably going to have to use EARLIER so...See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Hi Greg,
All the Attribute/Indx with the same value needs to be substracted. I am hilighting the rows to show how the calculatin is being performed.
I am just finding it difficult to apply DAX, as a work around I am using SQL to get the same result...but it is making the data model awkward.
- Greg_Deckler
Right, got it. Read the article, it will get you to where you need to be. Basically, you want to use EALIER to filter out all other matching rows into a temporary table that you create with VAR. Then, use MINX to get the minimum Date. Then use FILTER to filter the temp table to that minimum date and finally use any aggregation to get the value in that row and subtract the current row's value. The article referenced demonstrates this procedure.
v-yuta-msftCommunity Support
Hi Chirantan,
To achieve your requirement, you can create a calculate column as below:
Calculation = VAR First_Value = Table1[Value] VAR First_Index = Table1[Index] VAR Last_Value = CALCULATE(MAX(Table1[Value]), FILTER(Table1, Table1[Index] = First_Index + 4)) RETURN IF(Last_Value = 0, 0, Last_Value - First_Value)
Regards,
Jimmy Tao