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 IV
Super User IV

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 III
Super User III

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 III
Super User III

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
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!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors