cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Karthiknfr Regular Visitor
Regular Visitor

Dynamically changing Months based on X axis of line chart based on month selected on slicer

Hello,

 

Dynamically changing Months based on X axis of line chart based on month selected on slicer. Like if i select october 2019 my x axis should display last 6 months till october. Example from May2019 to Oct2019. Similarly if i select september in slicer it should display all months from April 2019 to September 2019.

 

Thanka,

Karthik

7 REPLIES 7
Super User
Super User

Re: Dynamically changing Months based on X axis of line chart based on month selected on slicer

Hi @Karthiknfr ,

 

For making this work you need to create a disconnected table for you slicer and then create a measure based on that selection.

 

Let's assume you have a sales file with dates and sales if you add the measure below to calculate your sales for the last 6 months as you can see below your chart will change for the last 6 months:

 

Sales last 6 months = 
CALCULATE (
    SUM ( Sales[Sales] );
    FILTER (
        ALL ( Sales[Date] );
        Sales[Date] <= MAX ( DimDate[Date] )
            && Sales[Date] >= EDATE ( MAX(DimDate[Date]); -6 )
    )
)

Sales_Last_6_Months.gif

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Dynamically changing Months based on X axis of line chart based on month selected on slicer

Hi,

You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Karthiknfr Regular Visitor
Regular Visitor

Re: Dynamically changing Months based on X axis of line chart based on month selected on slicer

Though this changes X axis dynamically, did not help achieve my results. I have Rolling 3 month and 6 month averages calculated. And have put it as a slicer. Now when i try to do something similar to what you have done it is not working.

Super User
Super User

Re: Dynamically changing Months based on X axis of line chart based on month selected on slicer

Hi,

Whom are you responding to?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Karthiknfr Regular Visitor
Regular Visitor

Re: Dynamically changing Months based on X axis of line chart based on month selected on slicer

I responded to @MFelix . But any advise is appreciated 

Super User
Super User

Re: Dynamically changing Months based on X axis of line chart based on month selected on slicer

Hi,

Please go through the solution at the link which i have shared with you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User
Super User

Re: Dynamically changing Months based on X axis of line chart based on month selected on slicer

Hi @Karthiknfr ,

 

If you have the measures calculated based on a slicer you can do a change to have something similar to this:

 

Sales last 6 months =
CALCULATE (
    IF (
        CONTAINS ( Table; Table[Column]; "Rolling 3 month" );
        [Rolling 3 month];
        [Rolling 6 month]
    );
    FILTER (
        ALL ( Sales[Date] );
        Sales[Date] <= MAX ( DimDate[Date] )
            && Sales[Date] >= EDATE ( MAX ( DimDate[Date] ); -6 )
    )
)

 

Change the Table and column by the one of the slicer of the rolling slicer.

 

Regards,

MFelix 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 137 members 1,412 guests
Please welcome our newest community members: