Mileage Calculation

[Anonymous]

Hi Community,

I have the below two datasets

Dataset 1 

Vehicle Reg	Start Date	End Date	Distance (Miles)
123	01/01/2021	01/01/2021	10
123	02/01/2021	02/01/2021	20
123	02/01/2021	02/01/2021	30

Dataset 2 

Vehicle Reg	Daily Commute (Miles)
123	3
124	2
125	4

I need to create a calulcation showing me total amount of mileage covered per vehicle reg in dataset 1 - vehicle reg's daily commute for each day they have travelled.

Any ideas on how this can be achieved?

Many thanks,

Elliot 

[PaulDBrown]

you clarify the calcualtion? Is it just the daily mileage minus the daily reg value?

[Anonymous]

Hi,

Yes, the calulcation is as follows

Sum of Distance Miles (dataset1) - commute miles (dataset2)        

Please note the commute miles (dataset2) will need to be minused for every active day in dataset 1.

Hope this makes sense.

Elliot 

[PaulDBrown]

Sorry, what do you mean by "will need to be minused for every active day in dataset 1"?

Also, will there be unique Vehicle reg values in Dataset 2 or will there be duplicates?

[Anonymous]

Example below

Dataset 1 

Vehicle Reg	Start Date	End Date	Distance (Miles)
123	01/01/2021	01/01/2021	10
123	01/01/2021	01/01/2021	15
123	02/01/2021	02/01/2021	20
123	02/01/2021	02/01/2021	30

Dataset 2 

Vehicle Reg	Daily Commute (Miles)
123	3
124	2
125	4

Sum would be as follows

Sum of Distance Miles in dataset 1 = 75

75 - Daily Commute miles for every day mentioned in dataset 1 = 69

For every vehicle reg in dataset 1 there will be a the same vehicle reg in dataset 2 along with its daily commute mileage.

[PaulDBrown · Accepted solution]

See if this works.

The model

The measures 

Mileage =
SUM ( 'Dataset 1'[Distance (Miles)] )

Daily Commute = SUM('Dataset 2'[Daily Commute (Miles)])

RegDaily Commute =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Dataset 1', 'Dataset 1'[Start Date], 'Dataset 2'[Vehicle Reg] ),
        "DC", [Daily Commute]
    ),
    [DC]
)

Net Mileage =
[Mileage] - [RegDaily Commute]

to get..

I've attached the sample PBIX file

[Anonymous]

Hi Paul,

After putting the formula to the test with real data I am getting a strange result.

The formula is working correct as seen in the below table screenshot (some vehicles will not have daily commute miles allocated) but for some reason the sum of all RegDailyCommute is alot higher than all of the values added together.

Any ideas?

[PaulDBrown]

@Anonymous 

I'm not sure what's going on at your end. I've just added a new reg with no daily commute and the measures work.

 Can you post the code you are using for the [RegDaily commute] measure?

[Anonymous]

Hi,

I have changed the relationship settings to 'both ways' this seems to have solved it!

Many thanks,

Elliot 

[Anonymous]

Thats spot on!

Thank you for taking the time in your day to help me!

Elliot