Nested Dynamic Partition by on a virtual Table

[Anonymous]

I Have a table named "Trips" having Columns "Vehicle ID" , "Date" , "Distance" , "Fuel_Economy". Since a Vehicle can run on multiple dates so Vehicle ID can repeat so is Date. 

Total_Fuel_Consumed is a new quantity which has to be calculated as (Sum of total Distance travelled by a Vehicle between selected dates (Including both) / Fuel_Economy of a particular date) and finally I want to Return the SUM of  (Difference between Total_Fuel_Consumed of two dates) For all the Selected Vehicle IDs . These dates will be picked by the end user from a slicer. A snap of the same is below. If a particular vehilce has not run on selected DATE2 then measure should take The Latest date for which the vehicle has run between the selected date range for Total_Fuel_Consumed Calculation and Similarly  For Date1 it should take the oldest Date available for that vehicle ID between the selected date range.

I have tried doing it by Partion BY . But unable to reach the dynamicity of Partitioning based on seleted date range. Please help me with this. I am currently able to get this result for all the dates with this measure. But I want to get a dynamic output based on selected dates. 

Fuel_Saved =

VAR DateFuelMax =

SUMMARIZE(FuelSavedtable,

        FuelSavedtable[vehicleId],

        "Maximum Date", MAX('FuelSavedtable'[Date_col]),

        "Fuel Consumed Max Date" , CALCULATE(AVERAGEX(FuelSavedtable , FuelSavedtable[Fuel Consumed when total distance travelled at this Day's FE]),(FuelSavedtable[Date_col]=MAX(FuelSavedtable[Date_col]))))

VAR DatefuelMaxMin =

                ADDCOLUMNS(DateFuelMax,

                "Minimum Date", MIN('FuelSavedtable'[Date_col]),

                "Fuel Consumed Min Date" , CALCULATE(AVERAGEX(FuelSavedtable , FuelSavedtable[Fuel Consumed when total distance travelled at this Day's FE]),(FuelSavedtable[Date_col]=MIN(FuelSavedtable[Date_col]))))

VAR Result = SUMX (

        DatefuelMaxMin , [Fuel Consumed Min Date] - [Fuel Consumed Max Date]

)       

RETURN Result

[Anonymous]

Solved using multiple measures.

[v-zhangti]

Hi, @Anonymous 

Based on the data you provided, can you give examples of what you expect the output to be?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hey @v-zhangti , Lets say the user selects 16th Dec 2022 from DATE1 filter and 24th Dec 2022 from Date 2 filter and Vehicle IDs 1,2,3. The Measure should return 

SUM OF {(Sum of Distances travelled between 16th to 24th Dec by Vehicle ID 1 whenever Vehicle ID 1 has run including 16th and 24th) / (Fuel_Economy of Vehicle 1 at 16th Dec if Vehicle ID 1 has run on 16th Dec OR The earliest date at which Vehicle ID 1 has run  after 16th Dec)}   - {( Sum of Distances travelled  between 16th Dec to 24th Dec by Vehicle ID 1) /(Fuel_Economy on 24th Dec if Vehicle ID 1 has run on 24th Dec OR The latest date at which Vehicle ID 1 has run  before 24th Dec)} ) For all Vehicles 1 ,2 , 3.

 MATHEMATICALLY it would be 

Vehicle ID 1 :- ( 95 / 2)-(95/1) = -47.5  { AS Vehicle ID 1 has run on 20,21,22,23,24 Dec Between 16th and 24th for a cumulative distance of 95 Km, The Earliest Date on which it has run Between 16th and 24th is 20th Dec, and Fuel_Economy on 20th Dec is 2 Km/L so Fuel_Economy is taken as 2 in the first bracket.  Similary Fuel_Economy for the Latest Date of travel between 16th and 24th Dec is 1 Km/L , So 1 has been taken in the second bracket. 

Vehicle ID 2 :- ( 67 / 7)-(67/4) = -7.17

Vehicle ID 3 :- (  157 / 5)-(157 / 6) = 5.23

FINAL VALUE TO RETURN = Vehicle ID 1 + Vehicle ID 2 + Vehicle ID 3 = (-47.5)+(-7.17)+(5.23) = -49.44

Hope this helps