Adjust for usage

[Big_Trucks]

Hi All,

I could really use some help on this line graph I am attempting to build. The idea is based on a service plan for a vehicle using milage as the input variable. Depending on how much the truck is driven determines it service schedule which is fixed at milage intervals, and the dollars spent over the life of the service plan. If a given truck is estimated to drive 100 miles a month, I have an outlined service plan in blue below. If the truck is driven less than expected (or more), I want to shift the graph from today to account for the lower(or higher) number of miles driven over the last 3 month avg (say 80 miles). How would I be able to make sure the starting point stays the same, and just shift the graph over today as indicated in red to account for the adjustment?

Also to note, my dollars are accounted for in a measure. I do not want to bring in all the leading zero values from my date table as you can see. 

If further info is needed feel free to ask. 

P.S. I added the data as requested. You can see how the actual run miles happened later than projected in time. Thus would shift the time schedule of the service milestones.

[v-shex-msft · Accepted solution]

HI @Big_Trucks,

What type of value are you stored in date fields? If they are date values, you can create an unconnected table(it does not have relationships to the raw table) with date value as a selector to choose when the graph started.

Measure =
VAR selected =
    MAX ( selector[Date] )
VAR currDate =
    MAX ( table[Actual Year QTR] )
VAR rolling =
    CALCULATE (
        SUM ( table[Milage Actual] ),
        FILTER (
            ALLSELECTED ( table ),
            [Actual Year QTR] >= selected
                && [Actual Year QTR] <= currDate
        )
    )
RETURN
    CALCULATE (
        SUM ( 'service'[Expense] ),
        FILTER (
            ALLSELECTED ( 'service' ),
            [service milestones]
                <= INT ( rolling / 100 ) * 100
        )
    )

Notice: you can use the 'Actual mile' record table data field as the axis of the visual. 
Regards,

Xiaoxin Sheng

[parry2k]

@Big_Trucks I would recommend to provide sample data and expected output,  you can put together something in excel.