Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

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

4 years ago

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

Mileage.pbix28 KB

Anonymous

Not applicable

4 years ago

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?