Line Chart Cumulative sum

[alk]

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 other forum answers regarding running total cumulative sums and used the following code. I forgot to mention but the product records have different statuses for ex. Shipped vs In Production (but that's not important as I use page level filters), hence the status over time field.

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

The line chart is okay if I look at it over a broad year, but in months due to some months not having any new product records, the line dips to 0 at these months. What I want is that month to just show the previous months running total if no new records are created. How should I alter my code?

Thanks in Advance

[v-yulgu-msft]

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]

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

[gumbow]

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! 

[v-yulgu-msft]

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