Forum Discussion

alk's avatar
alk
Frequent Visitor
8 years ago

Line Chart Cumulative sum

Hello all,   I am currently working with data that looks at the creation of new product records over time and wanted to make a line chart that shows running total over months. So I tried looking at...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi alk,

 

Not very clear about the usage of [Status Over Time] in above formula. Maybe you could provide more detailed sample data to illustrate your scenario.

 

In my test, I created a calendar table which is related to 'Report' based on dates. Then, create below measure:

Running Total SUM =
CALCULATE (
    SUM ( Report[Creates Count] ),
    FILTER (
        ALL ( CalendarTable ),
        MONTH ( CalendarTable[Date] ) <= MONTH ( MAX ( CalendarTable[Date] ) )
    )
)

Add date from calendar table and above measure to line chart.

 

Best regards,

Yuliana Gu

alk's avatar
alk
Frequent Visitor
8 years ago

Hello v-yulgu-msft ,

 

Sorry for clarity purposes the [Status Over Time] field is an updating list of dates based on the date the product status was changed. Therefore, the date the record for the product was created there would be no value(as record creation date is not recorded from the data we pull), the date the product is in production the date will change to that date and if the product is shipped, the [Status over Time] will be changed to the date it was shipped. The created count changes to 1 if the product is in production and 0 if the product is not. If a product has been shipped, it was in production at some point and should be included in the running total

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi alk,

     

    Running Total SUM = CALCULATE (SUM(' Report'[Created Count]), FILTER( ALLSELECTED(' Report'), ‘Report’[date_added] <= MAX ( [Status Over Time]) && MIN([Status Over Time]) <= TODAY()))

     

    I still have some concern:

    1. There is no [date-added] column in sample table. 
    2. What does the highlighted part mean? 
    3. Should we consider the "Product status", whether it is "In production" or "shipped" when calculating running total?
    4. Please use a column list to show us your desired result (the correct running total value) based on above sample.

     

    Regards,

    Yuliana Gu

  • gumbow's avatar
    gumbow
    New Member
    3 years ago

    I have this exact same challenge!

     

    I used a quick measure to generate the running total, and it works for the full data set (cumulative sum of hours), but soon as i start selecting related visual elements from other charts, my line chart creates the dips as well 😞

     

     
    [Time savings [h/year]] running total in Stage I Start date 2] =
    CALCULATE(
        SUM('Automation Project Portal'[Time savings [h/year]]]),
        FILTER(
            ALLSELECTED('Automation Project Portal'[Stage I Start date]),
            ISONORAFTER('Automation Project Portal'[Stage I Start date], MAX('Automation Project Portal'[Stage I Start date]), DESC)
        )
    )

     

    Intent is to similarly maintain the cumulatively rising line chart when table values are being filtered or selected.

     

    running totals chart for entire dataset

     

     running totals chart when data set is filtered/subset of data is selected

     

     

    many thanks in advance!