Adding journey number after returning to base each time

[Tejaswini_Dahat]

Hi everyone,
I am trying to add a journey number for the time period to the Vehicles.
One journey would be completed when it is left from the base and returned to the base.
Attached is the expected column "Journey Number".

Thanks

  

[v-qiuyu-msft · Accepted solution]

Hi @Tejaswini_Dahat, 

You can create calculated columns like below: 

C1 = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Vehicle No]=EARLIER('Table'[Vehicle No])&&'Table'[TIMESTAMP]<=EARLIER('Table'[TIMESTAMP])))

C2 = var nextvalue=CALCULATE(MAX('Table'[LOCATION]),FILTER('Table','Table'[Vehicle No]=EARLIER('Table'[Vehicle No])&&'Table'[C1]=EARLIER('Table'[C1])+1))
return IF('Table'[LOCATION]="BASE" && nextvalue<>"BASE",1,0)

C3 = SUMX(FILTER('Table','Table'[Vehicle No]=EARLIER('Table'[Vehicle No])&&'Table'[TIMESTAMP]<=EARLIER('Table'[TIMESTAMP])),'Table'[C2])

Best Regards,
Qiuyun Yu

[Tejaswini_Dahat]

Hey @v-qiuyu-msft ,  Thank you so much, for your efforts & this solution works perfectly 🙂