Compare sum of values between dates in 2 different tables

[LuisSalcido]

Hi, Im kinda new to Power BI and encountered an issue I can't solve.

I work for a logistics company and I extract data about the fuel consumption of semi-trucks from the internal computer and register it on an excel spreadsheet. I also have an excel spreadsheet where I register the fuel purchases done by every truck/driver. I want to compare how much the computer says that the truck consumed with what the driver purchased to see if it matches.

The closest I could get to this was creating a relationship between the "date" column of the tables and creating a slicer with a clustered column chart to see the fuel consumed at the date selected (which is the fuel consumed/bought between that date and the previous one. The problem here is that the chart only shows the last number in the range selected, but I don't extract data every single time the truck is refueled. 

For example, in the following photo, I select a date and the chart shows the value on that date only. The light blue column is the fuel consumed according to the truck's computer between that date and the previous one. The dark blue one shows the fuel purchased on that date, but it's missing a value between that date and the last one (tuesday, january 21).

I also want to show the difference between the 2 columns.

Any suggestion? Thanks!

[v-priyankata]

Hi @LuisSalcido 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[pankajnamekar25 · Accepted solution]

Hello @LuisSalcido ,

To solve this issue, you need to establish a proper data model by creating a separate Date Table and linking it to both the Fuel Consumption Table and Fuel Purchase Table. This ensures a common timeline for comparison. You then create measures to calculate total fuel consumed and purchased:

DAX

Total Fuel Consumed = SUM(FuelConsumptionTable[Fuel_Consumed]) 

Total Fuel Purchased = SUM(FuelPurchaseTable[Fuel_Purchased]) 

Since the challenge is capturing fuel consumption between refueling dates rather than just the selected date, a measure is needed to sum fuel usage between the current and previous date:

DAX

Fuel Consumed Between Dates = 

VAR SelectedDate = SELECTEDVALUE('DateTable'[Date]) 

VAR PreviousDate = CALCULATE(MAX('DateTable'[Date]), 'DateTable'[Date] < SelectedDate) 

VAR Fuel_Consumed = 

    CALCULATE( 

        SUM(FuelConsumptionTable[Fuel_Consumed]), 

        FuelConsumptionTable[Date] > PreviousDate && FuelConsumptionTable[Date] <= SelectedDate 

    ) 

RETURN Fuel_Consumed 

To highlight discrepancies, you can compute the difference between purchased and consumed fuel:

DAX

Fuel Difference = [Total Fuel Purchased] - [Fuel Consumed Between Dates] 

A Clustered Column Chart with the Date Table on the X-axis and the measures for fuel consumed and purchased will visually represent the data, allowing comparisons. A Date Slicer can be used to dynamically filter the report, ensuring the analysis reflects fuel consumption between refueling dates rather than isolated daily values.

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[LuisSalcido]

Hi @pankajnamekar25 ! Thanks for the help. This helped me get very close to the goal but it's not quite there yet. The "Fuel consumed between dates" works great and gives me the right information. However, I'm missing a variable for "fuel purchased between dates" so that the "fuel difference" calculates the difference between those 2 variables instead of using "total fuel purchased". I tried using a formula similar to the one used for fuel consumed between dates but it doesn't quite work. Could you help me with a possible solution for that?