Subtracting LAST ID in same column

[senad]

ID	AgeAtEvent	EventType	Differenrce
101	10	1st Tag	-
102	10	1st Tag	-
103	10	1st Tag	-
101	16	2nd tag	6
104	10	1st Tag	-
105	11	1st Tag
103	14	2nd tag	3
101	20	3rd Tag	4

Best way to achieve this output in Power BI? I have the 1st 3 columns, looking to calculate the 4th

Essentially, each row represents an event trigger, and I would like to find the difference of event age. So 2nd Event minus 1st event, but if no other event comes before it, then prdouce a blank. 

ID 101 for example, has 3 events, so I would like the difference from 1st event to 2nd, 2nd to 3rd, etc. 

Thank you in advance!

[Nathaniel_C · Accepted solution]

Hi @senad ,

Like this?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[senad]

Hi @Nathaniel_C  this works great as a measure, thank you so much

Is there a way to do it as a calculated column, though?

Let me know if you can!

[Nathaniel_C · Accepted solution]

Hi @senad ,

Please try this:

mydifcolumn = 

var _ID = (TagTable[ID])
var _ageAtEvent = (TagTable[AgeAtEvent])
var _EventTypeOnly = (TagTable[EventType # only])

var _previousEventTypeOnly = _EventTypeOnly-1 //get the previous Event Type

var _calc = CALCULATE(MAX(TagTable[AgeAtEvent]),FILTER(all(TagTable),TagTable[EventType # only]=_previousEventTypeOnly && TagTable[ID]=_ID)) // Get the Age At Event for the previous row of the same ID

Return if (_previousEventTypeOnly>0, _ageAtEvent- _calc) 

Basically the same thing, but take out the MAX() surrounding all the column names that are brought in as variables (var)

Good Luck! And as this is a second solution, would you please mark this as well?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[senad]

Yes! Thank you so much. Appreciate the explanation as well. Have a good one

[Nathaniel_C]

Hi @senad ,

You are very welcome!

And the reason that the MAX() is not needed, is that when creating a calculated column, Power BI "knows" which row it is on when it does the calculation.

Cheers,

Nathaniel

[senad]

Yes! Thank you @Nathaniel_C .

I have an EventType #Only column as well! How did you make this work? 

Best,

 Senad

[Nathaniel_C]

You are welcome! Did my second post answer your question @senad ? Nathaniel

[Nathaniel_C · Accepted solution]

Hi @senad ,

Like this?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @senad ,
I would note that the value you have for 103 Difference should be 14-10=4.

mydif = 

var _ID = max(TagTable[ID])
var _ageAtEvent = MAX(TagTable[AgeAtEvent])
var _EventTypeOnly = MAX(TagTable[EventType # only])

var _previousEventTypeOnly = _EventTypeOnly-1 //get the previous Event Type

var _calc = CALCULATE(MAX(TagTable[AgeAtEvent]),FILTER(all(TagTable),TagTable[EventType # only]=_previousEventTypeOnly && TagTable[ID]=_ID)) // Get the Age At Event for the previous row of the same ID

Return if (_previousEventTypeOnly>0, _ageAtEvent- _calc) 

Previous to creating this measure, I extracted the numeric value in Power Query from the column EventType to a new column EventType # Only and made it a whole number.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel