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
you clarify the calcualtion? Is it just the daily mileage minus the daily reg value?
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?