Join two table with special criteria

[viferenc]

Dear All,

I´m kindly asking for your support

I have two tables imported in Power BI Desktop.

in one of the table I have a list of transports done by the trucks.

I have the transport ID, truck ID and starting and endig KM of the transport

Example:

Transport ID	Truck ID	Starting KM	Ending KM
2021-1252	ABC-277	119818	120248

Second table shows me the refuling information of the trucks.

Truck ID	Fueling (liter)	KM, when fueling was done
ABC-277	94	118 102
ABC-277	65	118 293
ABC-277	132	118 663
ABC-277	78	118 919
ABC-277	70	119 428
ABC-277	49	119 657
ABC-277	47	119 853
ABC-277	134	120 248
ABC-277	177	120 813
ABC-277	149	121 190
ABC-277	75	121 977
ABC-277	112	122 304

The problem is that during one transport the truck can refuel (we use the logic that they refuel it always to 100%) the tank not only once, but several times. It can happen that the fueling was done before the transport was started or during the transport.

In this exampleduring this transport the truck did it two times.

I would like combine the two table in the following way, because I would like to calculate the consumption (used liter) for each transport.

The blue columns are the combined table, the red columns are just calculation.

ID	Truck ID	Starting KM	Ending KM	Fueling KM low	Fueling KM high	Fueling (liter)	KM diff between refueling	Consumption (l/km)	Used KM	Used Liter
2021-1252	ABC-277	119818	120248	119657	119853	46,65	196	0,24	35	8,33
2021-1252	ABC-277	119818	120248	119853	120248	133,62	395	0,34	395	133,62

It would be really a big, if somebody could help me.

Thank you in advance

Feri

[v-zhangti · Accepted solution]

Hi, @viferenc 

You can try the following formula.

KM diff between refueling = [Fueling KM high]-[Fueling KM low]

Consumption (l/km) =
DIVIDE ( [Fueling (liter)], [KM diff between refueling] )

Used KM =
IF (
    [Fueling KM high] >= [Ending KM],
    [Fueling KM high] - [Fueling KM low],
    [Fueling KM high] - [Starting KM]
)

Used Liter = [Used KM]*[Consumption (l/km)]

Best Regards,

Community Support Team _Charlotte

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

[lbendlin]

There are a lot of things wrong with this chart but it illustrates some of the issues with your request.

First of all the consumption is not linear between refuellings. But in the absence of further data that is what you would have to assume.  So you would have to take the first refueling at 119853  and its consumption (0.2379) but only for the interval between 119818 and 119853. Then all the consumptions up until the next refill after the trip is completed (which in your case coincides with the end of the trip but that is rather unlikely in real life), again prorated for only the fractional kilometers.

A lot of effort for an inherently imprecise result...

See attached for a failed attempt.