cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wsushox1 Helper I
Helper I

Running Total Chart over Multiple Years

Hello all,

 

Question regarding both DAX and getteing the correct chart.

 

Problem:  I want to plot multiple cumulative YTD sales plots on one plot/tile.  I have succesfully been able to do that for one FY but that is dependent on having a slicer of filter on it.  I'd like to do it for multiple years. The image Below should help illuminate where I am at and where I'd like to go:

 

Daily Sales_FY.PNG

 

The image above shows that I can plot one year of sales data over whatever period I have selected in the slicer.  In this case, the 2018-2019FY.

 

However, I'd like to plot the chart in blue ( I don't care about # of sales each day which is what the green line shows) fo the past five years. Ideally I'd have five line graphs with that same shape on the same plot.  Our sales are moving earlier in the fiscal year fall and later in the spring.  I'd like to be able to show this visually to executives.

 

Any help or ideas on how to set this up?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Running Total Chart over Multiple Years

I tried creating an example of what you need. Can you refer to page and let me know what exactly you are looking for. I created two measures and added them to two graphs. https://www.dropbox.com/s/tu56wooipvoyoy3/CompareRange_timedim_YearTillDate.pbix?dl=0

Screenshot 2019-10-04 23.56.01.png





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Running Total Chart over Multiple Years

Can you please share the formula.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


wsushox1 Helper I
Helper I

Re: Running Total Chart over Multiple Years

Sure. @amitchandak 

 

The code I am using to calculate the running total sales are the following:

 

Measure = CALCULATE( SUM(SalesPerDate[Initiates]), FILTER (ALLSELECTED(SalesPerDate), SalesPerDate[Date] <= MAX(SalesPerDate[Date])))
 
This is giving me the desired outcome of calculating the annual fiscal year todate running total per date with date on the x axis and sales on the y axis.  However, I want to add multiple years - not only the year that I select on the filter/slicer.
 
2019_United_States_tornado_count
 
Ideally it would look like this....except for tornados it would be sales. Each line would be a different year.  Does this make sense?
Super User IV
Super User IV

Re: Running Total Chart over Multiple Years

I tried creating an example of what you need. Can you refer to page and let me know what exactly you are looking for. I created two measures and added them to two graphs. https://www.dropbox.com/s/tu56wooipvoyoy3/CompareRange_timedim_YearTillDate.pbix?dl=0

Screenshot 2019-10-04 23.56.01.png





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

Super User IV
Super User IV

Re: Running Total Chart over Multiple Years

Refer page 9





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors