Subtract rows in a Table based on a id

[Chirantan]

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.

[Greg_Deckler]

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/3395...

[Chirantan]

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.

[v-yuta-msft · Accepted solution]

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

[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.