Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ecmurillo
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
v-xicai
Community Support
Community Support

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
v-xicai
Community Support
Community Support

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.

Ashish_Mathur
Super User
Super User

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/

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

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/

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

amitchandak
Super User
Super User

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

 

 

darentengmfs
Post Prodigy
Post Prodigy

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.

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.