cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Create a cumulative stacked line graph in Power bi

Hi Can anyone help me how to create a cummulative stacked line chart in Power bi?
basically, I just wanted to replicate the graph below.

And I want to do these in running monthly.
Next update should be from September last year upto September current month.

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Create a cumulative stacked line graph in Power bi

Hi @ecmurillo ,

As darentengmfs's suggestion, there are details to add here. You may set the relative date for the date slicer, select last 1 years, which will filter data from the last 1 year until current month automatically, see: Creating a relative date slicer and filter in Power BI .

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 REPLIES 8
Highlighted
Continued Contributor

## Re: Create a cumulative stacked line graph in Power bi

Do they following steps:

1. Go to Power Query, sort your data by date ascendingly
2. Add an Index Column, apply the query
3. Create a Running Total column with this formula: Running Total COLUMN =CALCULATE (SUM ( 'Table'[Value] ),ALL ( 'Table' ),'Table'[Index] <= EARLIER ( 'Table'[Index] ))
4. The formula above will give you the running total
5. Insert an area chart, use the Running Total Column as your value, Date as your Axis, and your Legend (Canceled, Open, Closed)

If it's not giving you the sequence you need in the chart, do this

1. Go to Power Query
2. Add a new column: if [Legend] = "Canceled" then 1 else if [Legend] = "Open" then 2 else if [Legend] = "Closed" then 3 else 9999
3. Apply the query. Go to Table View, select your Legend column, go to Column Tools, Sort by Column, Sort by the new column you just created

When this is done, click on the visual, put Date into "Filter on this Visual", change filter type to Relative Date, choose "in the last", put 1, and choose "year". Check "Include today". This will give you the 365 days you want.

Highlighted
Super User IX

## Re: Create a cumulative stacked line graph in Power bi

@ecmurillo , Typically do cumulative like this with a date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

if you want it to reset every year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30")) // or 8/31
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))

Proud to be a Super User!

Highlighted
Super User V

## Re: Create a cumulative stacked line graph in Power bi

Hi,

Where should the accumulation start from?  Is it July of every year?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Frequent Visitor

## Re: Create a cumulative stacked line graph in Power bi

I can't get the visual I needed.
So far this is what i've got.

I want it to start from September 2019 upto September 2020

Highlighted
Frequent Visitor

## Re: Create a cumulative stacked line graph in Power bi

It should start from September 2019 upto September 2020.
Then I have to update it next month which starts from
October 2019 - October 2019.

and so on

Highlighted
Continued Contributor

## Re: Create a cumulative stacked line graph in Power bi

Please put a date filter in your chart like how I suggested. Use Relative Date and the filter should be in the last 1 year.

Highlighted
Super User V

## Re: Create a cumulative stacked line graph in Power bi

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Community Support

## Re: Create a cumulative stacked line graph in Power bi

Hi @ecmurillo ,

As darentengmfs's suggestion, there are details to add here. You may set the relative date for the date slicer, select last 1 years, which will filter data from the last 1 year until current month automatically, see: Creating a relative date slicer and filter in Power BI .

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021