Forum Discussion
Mileage Calculation
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
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
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?
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.
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
Thats spot on!
Thank you for taking the time in your day to help me!
Elliot