Forum Discussion
Mileage Calculation
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
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
9 Replies
PaulDBrownCommunity Champion
you clarify the calcualtion? Is it just the daily mileage minus the daily reg value?
- 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?