Lookup Monthly Price in Daily data - Power Query

[UK06B1]

Hello All, 

need a little help, i have a data set which is per day (Fuel cost data) and i have a table which has Average Fuel Price with the date column currently set at 1st of the respective month.

In my model i have both tables linked to a Date table (every day of the year). 

What i would like to do in a table in power query (Or even in a data model in excel) is work out the usage (Total cost / Avg Fuel Price of that month). I cant seem to get the monthly fuel price into the table to work out the usage. See screen shots below - the relationships i have set up - Date TAbel to both consiolidated (Cost) data and then the Price Table. But the resulting pivot gives me of course strange results  - suggesting the date isnt working - but i have checked and they are all formatted as date, i even put a "start of month data in the consolidated data as i thought that might be the issue. I can technically get this to work in BI but i could do with this being in Excel so the user can eventually use it in Excel rather than needing to use Power BI. In the pivot i would like the columns to be , Date, Cost, Fuel Price, then Usage (Cost/Fiel Price). Is it possible using power Pivot? 

Any help would be appreciated. 

[UK06B1]

Hi thanks for getting back to me , answers to your questions  

1. The expense data is line item data per day for the cost of Fuel. THe data set does have the date of transaction. 

2. I only have the average fule frice for each month. So the average fule cost i am taking from another system - this is not daily data - so i made the date (1st of the month). I suspect this might be the issue? 

3. I only want to work out the estimated fuel usage for the month - i do not need the usage number by day. Therefore Total cost month divided by the average price for that month. 

Hope that confirms your points ? if not let me know. 

thanks again for your help. 

[Daryl-Lynch-Bzy · Accepted solution]

Hi @UK06B1 , 

It looks like we need to simply the Data Model to make the following work:

Add a Start of Month column to the Fuel Price table using either Power Query or DAX.  Power Query is easier.

Date	Fuel Price	Start of Month
1/1/2022	1.44	1/1/2022
...	...
28/2/2022	1.85	1/2/2022

The next step is to add the Monthly Average Fuel Price to the Consolidated Data.  To keep things simple at this stage, add a calculated column with the following:

"Average Fuel Price" = 
   CALCULATE ( 
     AVERAGE ( 'Fuel Price'[Fuel Price] ) , 
     'Fuel Price'[Start of Month] = 'Consolidated Data'[Start of Month] 
   )

This will add a column with Average Fuel price.  This can be used in Measure to calculate fuel useage:

Estimated Fuel Used = 
    SUMX ( 'Consolidated Data' , 
         DIVIDE ( 'Consolidated Data'[In PCLC] , 'Consolidated Data'[Average Fuel Price] )
   ) 

You can avoid adding the "Average Fuel Price" calculated column if this embedded within the SUMX function.   This DAX is more complex:

Estimated Fuel Used (hard) = 
   SUMX ( 'Consolidated Data' , 
       DIVIDE ( 'Consolidated Data'[In PCLC] , 
                CALCULATE ( 
                       AVERAGE ( 'Fuel Price'[Fuel Price] ) , 
                       'Fuel Price'[Start of Month] = 'Consolidated Data'[Start of Month] ) 
                 )
       )
   ) 

[Daryl-Lynch-Bzy]

Hi @UK06B1 , could you please confirm if the following is correct:

1. You have expense data which contain Total Expense Amount ($), Expense Type (i.e. include Fuel) and Date of Transaction.
2. You also have the "Average" Fuel Price per litre for each Date.
3. You want to used the "Average" and "Total" to estimate the Fuel consumed in "litres" for each Date to calculate the estimated Total for each Month.