Amount of refuel in trip

[DimaMD]

Hello community!

I need your help in correct count of amount of fuel in my table.

We have following data:

1)Name of Vehicle

2) Date of operation

3) Type of operation

4) Refueling point

5) Amount of refueled 

6) Start/end of trip

7) Result (as wanted)

We are interested in counting the amount of fuel which was refueled in points, which are not blank and not equal to "Base"

For example we have a trip which starts on 04.07.2022 and end's on 12.07.2022, so we have 2 rows which are not blank and not equal "Base". Result should be 198,08+16,07= 214,15 (as shown in "Result" collum)

The result of this count must be saved in cell of collum "Amount of refueled", which row is marked as "End" in "Start/end of trip" collum (in current table you can see "0" in that cell)

As an example, the trip dated as 12.07.2022 must have result 214,15 in "ammount of refueled" cell in row which marked as "End"

[Anonymous · Accepted solution]

Hi @DimaMD,

You can try to use the following measure formula to get the start/end period based on current date, then you can summary records include in this range except the base point:

formula =
VAR currdate =
    MAX ( 'Export'[Data] )
VAR _start =
    CALCULATE (
        MAX ( 'Export'[Data] ),
        FILTER (
            ALLSELECTED ( 'Export' ),
            [Start/end of trip] = "start"
                && [Data] <= currdate
        )
    )
VAR _end =
    CALCULATE (
        MIN ( 'Export'[Data] ),
        FILTER (
            ALLSELECTED ( 'Export' ),
            [Start/end of trip] = "End"
                && [Data] >= currdate
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Export'[Refueled] ),
        FILTER (
            ALLSELECTED ( 'Export' ),
            [Data] >= _start
                && [Data] <= _end
                && [Refueling point] <> "Base"
        )
    )

Regards,

Xiaoxin Sheng

[DimaMD]

Hi? @Anonymous  Thank you, the measure works correctly