Monthly Miles conundrum

DAX DAX Calculations
1

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)

|Day|Split|
|---|---|
|Mon|19%|
|Tue|19%|
|Wed|19%|
|Thu|19%|
|Fri|19%|
|Sat|4%|
|Sun|1%|
2

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 =
ROUND (
    CALCULATE (
        DIVIDE ( MAX ( Miles[Miles] ), 7 ),
        FILTER (
            Miles,
            Miles[Vehicle ID] = EARLIER ( Miles[Vehicle ID] )
                && Miles[Miles] = EARLIER ( Miles[Miles] )
        )
    ),
    1
)

Lemme know if i understood the business problem correctly.

Regards,
Hemant

3

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.

DJ

1 Like
4

@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%.

2 Likes