Forum Discussion

Anonymous's avatar
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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • PaulDBrown's avatar
    PaulDBrown
    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

     

PaulDBrown's avatar
PaulDBrown
Community Champion
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

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thats spot on!

 

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

 

Elliot