Forum Discussion

SachinC's avatar
SachinC
Icon for Helper V rankHelper V
8 years ago
Solved

Vehicle Mileage field

Hi there,

I have some data by date and mileage. So an example would be:-

 

 05/10/2017  53344

  08/10/2017 59554

  09/10/2017 60561

  10/10/2017 64565

 

How can I effectively report the difference in mileage between one check to another please?

I tried to use the waterfall chart (to show cumulative) but it looked a mess.

 

Thanks.

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi SachinC,

    Based on my understanding, you want to get difference between two contiguous dates, right? I try to reproduce using your sample data.

    First, I create a calculated column to verify each date row.

    Index = RANKX(Test,Test[Date],,ASC)


    Then, create another calculated column to get the difference using the formula.

    Difference = Test[mileage]- LOOKUPVALUE(Test[mileage],Test[Index],Test[Index]-1)



    Finally, you can create a table to show your result as follows.


    >>I tried to use the waterfall chart (to show cumulative) but it looked a mess.

    You can a waterfall chart, add the difference field to Y value, please review the following screenshot.



    Best Regards,
    Angelia

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi SachinC,

    Based on my understanding, you want to get difference between two contiguous dates, right? I try to reproduce using your sample data.

    First, I create a calculated column to verify each date row.

    Index = RANKX(Test,Test[Date],,ASC)


    Then, create another calculated column to get the difference using the formula.

    Difference = Test[mileage]- LOOKUPVALUE(Test[mileage],Test[Index],Test[Index]-1)



    Finally, you can create a table to show your result as follows.


    >>I tried to use the waterfall chart (to show cumulative) but it looked a mess.

    You can a waterfall chart, add the difference field to Y value, please review the following screenshot.



    Best Regards,
    Angelia

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi SachinC，

    Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

    Thanks,
    Angelia