lookup valu of a previous row and calculate

[kesham]

Hi,

I'm new in power BI and i'm searching a solution to my issue

I'm seaching to calculate a empty kilometers between 2 travels of a driver making with a simple substraction.

I have the mileage counters of trucks (start and end when they are charging) and I want to calculate the distance between the "start" of the current travel and the "end" of the previous travel. See example in the picture

so for the user A, G7=D7-E2

a previous travel is depending the date and the time

thank you inadvance for your help

[kesham]

in short, what I need is:

"start kilometer" - select the max("end kilometer") of  the table (all "end kilometer" where "end kilometer" are < of "start kilometer")

[v-xicai · Accepted solution]

Hi @kesham ,

You can create column Rank , then create measure empty kilometers to meet your demand.

Rank = RANKX(FILTER(Table1,Table1[User]=EARLIER(Table1[User])),Table1[Date]&Table1[Time],,ASC,Dense)

empty kilometers = IF(MAX(Table1[Rank])=1,BLANK(),MAX(Table1[Start(Km)])-CALCULATE(MAX(Table1[End(Km)]),FILTER(ALL(Table1),Table1[User]=MAX(Table1[User])&&Table1[Rank]=MAX(Table1[Rank])-1)))

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ee4k5IBgXY5GvqVg4U...

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kesham]

Could someone help me please?