Difference between 2 rows of a column (elapsed distance)

[nerokasai]

Hi all!

I would like to create either a column or measure which returns the difference of distance run on a recorded event date. 

Here's a sample source table:

Company Name	Employee	Recorded Date	Difference (distance run on recorded date)	Total distance run (km)
A	Bob	2019-12-01	2	2
A	Bob	2019-12-02	5	7
A	Jack	2019-12-03	3	10
B	Hannah	2019-12-02	4	4
B	John	2019-12-05	3	7

Ideally, I would like to take data from the difference column to make a visual that represents the average distance ran on recorded date (average of the difference between two rows values) for each company or employee.

What would be the best way to approach this? Would I need Power Query for this?

Thank you!

[Greg_Deckler · Accepted solution]

OK, you can create the column you requested using EARLIER to filter ALL of your table and then using MAXX on that to grab the date (this gets you your previous row). Then you could use that date to filter out to the value for the last run. 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.... Let me know if you need further clarification but the technique is pretty much there. Although you might have an extra step to grab the previous run's distance.

[amitchandak]

@nerokasai , There are couple of solutions provided. Hope something has worked out for you

[nerokasai]

Works great! Thank you @Greg_Deckler  @amitchandak for your help!

[Greg_Deckler · Accepted solution]

OK, you can create the column you requested using EARLIER to filter ALL of your table and then using MAXX on that to grab the date (this gets you your previous row). Then you could use that date to filter out to the value for the last run. 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.... Let me know if you need further clarification but the technique is pretty much there. Although you might have an extra step to grab the previous run's distance.

[Greg_Deckler]

I am not 100% following this. Does the data displayed represent sample source data? Or is it what you would like to achieve? If it is sample source data, what is your expected output?

[nerokasai]

@Greg_Deckler My apologies, I should have better clarified! 

The data shown displays the overall expected output I want to have.

To be more precise, I would like the column "Difference (distance run on recorded date") added to the sample source data, such that the overall data table would be like what I showed.

[amitchandak · Accepted solution]

Some like this as mentioned by @Greg_Deckler 

 difference of distance run on a recorded event date = 
 var _last = maxx(filter(table,table[Company Name]=earlier(table[Company Name]) && table[Recorded Date]<earlier(table[Recorded Date]) ),table[Total distance run (km)])
 return
 if(is(_last),table[Total distance run (km)], _last-table[Total distance run (km)])