Totaling Initial value and subsequent readings in 2 tables

[adamgeiger]

I am having a hard time thinking through the approach to solving this problem. I am also blanking on how I would search for an existing approcah. 

I have 2 trucks that follow 3 routes and I am tracking the mileage (this is the overly simplified version, I have 15 trucks and 8 routes in real life). The mileage depends on how many miles were on when it was purchased (the initial value) and then I have monthly updates that tell me how many miles on each route. Each truck is dedicated to a set of routes. In the example I made the numbers the same, but they vary in real life, hence the need for tracking.

Table 1, imported from Excel.

Equipment	Date in Service	Initial mileage	Route 1	Route 2	Route 3
Truck A	1/31/2024	10,000	x	x
Truck B	3/31/2024	2,300		x	x

Table 2, imported from SP list. These are monthly totals for each route.

Date	Route 1	Route 2	Route 3
Dec-23	1000	500	100
Jan-24	1000	500	100
Feb-24	1000	500	100
Mar-24	1000	500	100
Apr-24	1000	500	100
May-24	1000	500	100
Jun-24	1000	500	100
Jul-24	1000	500	100

What I need is a current mileage for each truck with either power query or DAX. This will mean only adding the values from table 2 if they are after the in service date and only adding the route marked applicable in table 1.

The final results will look like this:

On Aug 1, the total for Truck A would be the sum of routes 1 and 2 for months Feb through Jul (9,000) plus the initial 10,000 making the total 19,000. For truck B it would be Apr to Jul on route 2 & 3 (2,400) plus 2,300 initial, total 4,700.

[dufoq3]

Hi @adamgeiger, Power Query solution:

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCikqTc5WcFTSUTLUNzbUNzIwMgGxDYAASFdAsYJSrA5MrROQb4yk1sgYrFQBrjw2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, #"Date in Service" = _t, #"Initial mileage" = _t, #"Route 1" = _t, #"Route 2" = _t, #"Route 3" = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcklN1jUyVtJRMjQwMABSpmASyFGK1YlW8krM0zUywSXrlpqER9Y3sQiPrGMBPlnfxEo8sl6l+FzlVZqDSzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Route 1" = _t, #"Route 2" = _t, #"Route 3" = _t]),
    ChangedTypeT1 = Table.TransformColumnTypes(Table1,{{"Date in Service", type date}, {"Initial mileage", type number}}, "en-US"),
    ChangedTypeT2 = Table.TransformColumnTypes(Table2,{{"Route 1", type number}, {"Route 2", type number}, {"Route 3", type number}}),
    ChangedTypeT2Date = Table.TransformColumns(ChangedTypeT2, {{"Date", each Date.FromText("01-"& _, [Format="dd-MMM-yy", Culture="en-US"]), type date}}),
    Ad_YearMonth = Table.AddColumn(ChangedTypeT2Date, "YearMonth", each Date.Year([Date])*100 + Date.Month([Date]), Int64.Type),
    StepBack = ChangedTypeT1,
    GeneratedMileage = Table.AddColumn(StepBack, "GenMileage", each 
        [ a = List.Transform(List.PositionOf(Record.ToList(_), "x", Occurrence.All), (x)=> Record.FieldNames(_){x}), //Current truck routes
          b = Table.SelectRows(Ad_YearMonth, (x)=> x[Date] >= [Date in Service]),                                    //Filterd T2
          c = Table.AddColumn(b, "MonthlyMileage", (x)=> List.Sum(Record.ToList(Record.SelectFields(x, a)))),        //Added monthly mileage for selected routes
          d = List.Buffer(c[MonthlyMileage]),
          e = List.Generate(
                    ()=> [ x = 0, y = d{x} + [Initial mileage] ],
                    each [x] < List.Count(d),
                    each [ x = [x]+1, y = [y] + d{x} ],
                    each [y]
          ),
          f = Table.FromColumns(Table.ToColumns(b) & {d} & {e}, Value.Type(b & #table(type table[Monthly Mileage=Int64.Type ,Total Mileage=Int64.Type], {})))
        ][f] ),
    Expanded = Table.ExpandTableColumn(GeneratedMileage, "GenMileage", {"YearMonth", "Monthly Mileage", "Total Mileage"}, {"YearMonth", "Monthly Mileage", "Total Mileage"}),
    RemovedColumns = Table.RemoveColumns(Expanded,{"Date in Service", "Initial mileage", "Route 1", "Route 2", "Route 3"})
in
    RemovedColumns

[v-stephen-msft]

Great insight, @Greg_Deckler .

My solution can show different results depending on the month, please check @adamgeiger .

Initially, I did the same thing as @Greg_Deckler in Power Query to unpivot the two tables.

Then create a measure to display the result. You can see, for truck A, July 2024 is 19000.

the total = IF(MAX('Table 2'[Date])>MAX('Table 1'[Date in Service]),CALCULATE(SUM('Table 2'[Value]),FILTER(ALLSELECTED('Table 2'),[Date]>MAX('Table 1'[Date in Service])&&[Date]<=MAX('Table 2'[Date])&&[Route] in  SELECTCOLUMNS( FILTER( 'Table 1', [X] = "x" ), "Route123", [Route] ) ))+MAX('Table 1'[Initial mileage]))

If you want to see on Aug 1, the total is 19000 instead of on July 2024. Just create a calculated column.

NewDate = EOMONTH([Date],0)+1

Best Regards,

Stephen Tao

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

[Greg_Deckler · Accepted solution]

@adamgeiger You need to do a couple unpivots and then the solution is pretty easy. Here is the measure and the full solution file (PBIX) is attached below signature.

Measure = 
    VAR __Date = MAX( 'Table 1'[Date in Service] )
    VAR __Initial = MAX( 'Table 1'[Initial mileage] )
    VAR __Routes = DISTINCT( SELECTCOLUMNS( FILTER( 'Table 1', [Value] = "x" ), "__Route", [Attribute] ) )
    VAR __Table = FILTER( 'Table 2', [Date] >= __Date && [Attribute] IN __Routes )
    VAR __Result = __Initial + SUMX( __Table, [Value] )
RETURN
    __Result

[adamgeiger]

Awesome, thank you! This worked well and I have a new tool in my belt.