Subtracting "2 rows" OF "same column" BUT based on "types of value in another column".

[one34codes]

Hi Community.

Please I need your help. I am totally pretty new and trying to fix a problem I am faced with.

I am trying to create a new column "Duration" which is based on the difference between values in the Time column as in the screenshot below however, I want it to rule to run on each capital (so if it is NEW YORK, then the calculation is based on all time where capital is new york and if CHICAGO, it runs on all time CHICAGO. As I cannot use IF as the cities are in 100s, I need a formula that can help solve this. (Note, the initial formula I applied, ran blanket on all capital such that where two capital overlaps, it still applies, creating wrong value).

I appreciate your help.

[Greg_Deckler]

@one34codes See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[one34codes]

Hi @Greg_Deckler  THanks alot for your reply. Sorry if I sound a bit dumb I am still a bit confused, I tried to run the formula in line with your tempate but  I was not sure exactly on the "previous date" part. I am attaching te file here, are you able to help please. I do appreciate this.

Time_difference_pbi_sample 

[Greg_Deckler]

@one34codes For the file you provided, the column formula is:

Duration in seconds = 
    VAR __Capital = [Capital]
    VAR __CurrentTime = [Time]
    VAR __Previous = MAXX(FILTER('Sheet1',[Capital] = __Capital && [Time] < __CurrentTime),[Time])
RETURN
    (__CurrentTime - __Previous) * 1. * 24 * 60 * 60

The __Previous variable is the previous time which is just the maximum value of the Time column where the Time is less than the current time (in the current row) and has whatever other criteria you want (like having the same Capital).

[one34codes]

Hi @Greg_Deckler it worked. You are a lifesaver ! I really need to sit down to learn this tool, it is very very interesting.

Please, if you don't mind, I have just one more question. The last row under duration in seconds is showing a huge value (3840833738), I assume this cell should rather be showing zero (0) as there is no preceding row. Do you know how I can solve this ? Thanks so much!

[Greg_Deckler · Accepted solution]

@one34codes Sure, that's easy, should have included that:

Duration in seconds = 
    VAR __Capital = [Capital]
    VAR __CurrentTime = [Time]
    VAR __Previous = MAXX(FILTER('Sheet1',[Capital] = __Capital && [Time] < __CurrentTime),[Time])
RETURN
    IF(ISBLANK(__Previous),0,(__CurrentTime - __Previous) * 1. * 24 * 60 * 60)

[one34codes]

@Greg_Deckler  Thank you so so much! For your time, and swift responses. 

I am grateful to this community, it's my first time, and so helpful.