Forum Discussion
Subtract rows in a Table based on a id
Hi I have a table as follows:
And I would like to achieve something like this:
I am trying to subtract rows based in the Attribute/indx, which in turn is trying to find the diffence between attribute/idx in each preceding year.
the above data is a subset of the original, with multiple InstituionNumber, Typekey and Date.
Thanks in advance.
P.S. new to powerBi and DAX.
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
4 Replies
- Greg_Deckler
Community Champion
So, 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.
