cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

ecmurillo_0-1600891529738.png

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


Please assist me. 
Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
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 .

198.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

8 REPLIES 8
Highlighted
Continued Contributor
Continued Contributor

Re: Create a cumulative stacked line graph in Power bi

Hi @ecmurillo 

 

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
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"))

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User V
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
https://www.linkedin.com/in/excelenthusiasts/
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.

 

ecmurillo_0-1600967962124.png

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
Continued Contributor

Re: Create a cumulative stacked line graph in Power bi

@ecmurillo 

 

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
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
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Support
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 .

198.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

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

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors