cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
one34codes
Frequent Visitor

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

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.

 

one34codes_0-1631735539007.png

1 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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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 

@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).


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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!

one34codes_0-1631791221582.png

 

@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)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!