Forum Discussion
Solved
Lookup Monthly Price in Daily data - Power Query
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 mon...
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] )
)
)
)
Hi UK06B1 , could you please confirm if the following is correct:
- You have expense data which contain Total Expense Amount ($), Expense Type (i.e. include Fuel) and Date of Transaction.
- You also have the "Average" Fuel Price per litre for each Date.
- 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.