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
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous 

 

I'm not sure what's going on at your end. I've just added a new reg with no daily commute and the measures work.

 Can you post the code you are using for the [RegDaily commute] measure?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi,

 

I have changed the relationship settings to 'both ways' this seems to have solved it!

 

Many thanks,

 

Elliot