Forum Discussion

pal95's avatar
pal95
Icon for Helper III rankHelper III
4 years ago
Solved

Cumulative Line Chart for Mileage Register

Hi,

I have a huge 'timesheet' like dataset where every employee registers the car they used and how many miles they made:

 

 

It generates a nice line chart where I can see mileage changes:

 

 

 

but my goal is to have the total mileage at the end, so I would like to have something like:

January 2022 - 0 miles,

February 2022 - 1250 miles,

March 2022 - 5000 miles,

April 2022 - 6000 miles, etc.

 

instead of 

January - 0 miles,

February - 1250 miles,

March - 3750 miles,

April - 1000 miles, etc.

 

Could you please help me with that? Thank you!

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    pal95 , Using a date table in measure and visual axis

     

    Cumm = CALCULATE(SUM(Table[Miles]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

     

    or

     

    Cumm = CALCULATE(SUM(Table[Miles]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

9 Replies

  • pal95's avatar
    pal95
    Icon for Helper III rankHelper III
    4 years ago

    Hi, just created a sample for you:

     

    DateRegistrationMiles
    1/17/2022A21
    1/11/2022A3
    1/12/2022A71
    1/17/2022A82
    1/5/2022B85
    1/6/2022A87
    1/17/2022B23
    1/8/2022B44
    1/9/2022B47
    1/4/2022B54
    1/11/2022C16
    1/12/2022C54
    1/17/2022C96
    1/4/2022A29
    1/15/2022C99
    1/16/2022B13
    1/17/2022B22
    1/4/2022C52
    2/19/2022C24
    1/20/2022B23
    1/21/2022A93
    2/17/2022C59
    1/23/2022C56
    1/24/2022A80
    1/25/2022C50

     

    Best regards,

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      pal95 , Using a date table in measure and visual axis

       

      Cumm = CALCULATE(SUM(Table[Miles]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

       

      or

       

      Cumm = CALCULATE(SUM(Table[Miles]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

      • pal95's avatar
        pal95
        Icon for Helper III rankHelper III
        4 years ago

        Hi, it doesn't work - the output looks the same as the original. I tried both. Also, I can't just put allselected('Date'), I have to add a table name before, does it matter?

  • keshavagrawal27's avatar
    keshavagrawal27
    Icon for Resolver I rankResolver I
    4 years ago

    Hello pal95,

    It can be acheived by using Bar Chart.

     

    If this helps you then please mark it as a solution and hit the thumbs up. Thanks.

    • pal95's avatar
      pal95
      Icon for Helper III rankHelper III
      4 years ago

      I need a line chart to then compare it with other data

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    4 years ago

    pal95 ,

    First create running total and then use the below formula to calculate above numbers,

    Runningtotal = 
CALCULATE (
    SUM ( Sample_Table[Miles] ),
    FILTER (
        ALL ( 'Sample_Table' ),
        Sample_Table[Date] <= MAX ( Sample_Table[Date] )
    )
)
    Totalcalculation = 
VAR _sumofa = SUM ( Sample_Table[Miles] ) RETURN _sumofa - [Runningtotal]

    Let me know if it works or not.

    Thanks,

    Arul

     

    • pal95's avatar
      pal95
      Icon for Helper III rankHelper III
      4 years ago

      It kind of works but seems to be going the opposite way, to negative numbers