Forum Discussion

UK06B1's avatar
UK06B1
Helper II
4 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

    DateFuel PriceStart of Month
    1/1/20221.441/1/2022
    ...... 
    28/2/20221.851/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] ) 
                     )
           )
       ) 

     

     

UK06B1's avatar
UK06B1
Helper II
4 years ago

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. 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

 

DateFuel PriceStart of Month
1/1/20221.441/1/2022
...... 
28/2/20221.851/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] ) 
                 )
       )
   )