Forum Discussion

shaebert's avatar
shaebert
Helper III
4 years ago
Solved

Line Chart Comparing Running Total

Hello, below is an example of data I have for a table for people who register for events from two different years. I am looking to create a line graph that compares cumulative/running total over time. So the graph would have two lines. 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    shaebert , You can get cumulative measure with help from date table 

     

    example

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

     

    or

     

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

     

    The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    shaebert , You can get cumulative measure with help from date table 

     

    example

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

     

    or

     

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

     

    The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

    • shaebert's avatar
      shaebert
      Helper III
      4 years ago

      Below is an example of one of the two lines based on the table of data above. Hope this helps clarify what I meant.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi shaebert ,

    Please try below steps

    1.my test table

    2.add a line chart visual and create a measure

    Measure =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= cur_date )
RETURN
    COUNTROWS ( tmp )

    If I have misunderstood your request, please feel free to let me know.

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • shaebert's avatar
    shaebert
    Helper III
    3 years ago

    I used the measure as the accepted solution as part of the solution to the problem. However, I had to figure out a couple of other things to get it to work correctly. For example, I wanted to group by week and wanted the running total to start over for each year. Two other posts helped me accomplish this. Then the trick was setting up correctly in the line cart. I had to use weekday numbers for the legend to get it to work correctly. 

     

    Thanks for the help above, it got me started!