Monthly Miles conundrum

Hi All,

I’ll try and make this as clear as possible, I’m working with vehicle data and need to get a calculation of how many miles each fleet completes in a Month.

The data is cumulative so if Vehicle A does 100 miles in month 1 and a further 150 miles in Month 2, the miles data will show 250 miles by end of Month 2. So, I’ll calculate the delta between Month 2 and Month 1 which will show me that Vehicle A did 150 miles in Month 2.

Straight forward so far…

The miles are ONLY updated weekly on a Tuesday so I cannot get a “True” monthly miles number. So the miles for each vehicle will be updated on Tuesday, say Vehicle A is updated with 250 miles on Tuesday, 250 miles will show on Wed, Thu, Fri, Sat, Sun & Mon…then the update will occur and Vehicle A miles will move to 350 miles for example.

In my head my thinking was along the lines of;

  1. Getting the miles for only WHOLE weeks that reside within each month
  2. Averaging those weekly miles out per day of the week according to the % split table I have included so 19% gets attributed to weekdays, 4% on a Saturday and 1% on a Sunday.
  3. Then extrapolating these average day of week miles across the rest of the month to create my “Monthly Miles”. So if Tuesday % split averages 125 miles and the month in question has 5 Tuesdays then it will multiply 125 by 5 and so on.
  4. The monthly averages would have to be specific to that individual month as miles can fluctuate due to seasonality
  5. I have to provide a delta between each month and the previous month.

I’ve enclosed a PBIX with a Total Miles measure, simple sum.

Any help on this, much appreciated.

Monthly Miles.pbix (82.5 KB)


Hi @DavieJoe ,

I guess if you make a calculated column within miles table for Average miles per day per week then i guess you get your desired result. This way you ensure that miles are properly distributed within the reported week and include seasonality too.

Average Miles Per Day Per week =
        DIVIDE ( MAX ( Miles[Miles] ), 7 ),
        FILTER (
            Miles[Vehicle ID] = EARLIER ( Miles[Vehicle ID] )
                && Miles[Miles] = EARLIER ( Miles[Miles] )

Lemme know if i understood the business problem correctly.


Hi @Hemantsingh thanks for the response

I need to utilise the % split of day of the week, which I forgot to include in my original post, this has now been added.

Your approach has given me some ideas to work on, thanks.

Will come back to you on this.


1 Like

@Hemantsingh Thanks for your input, I had been thinking of far more complex ways to solve this problem.

I’m going to add the calculated column but use a % day of the week split so Monday to Sunday are 18% of the value and Weekend days are 5%.