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

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

@Ashish_Mathur  I saw the link, and it is a tremendous solution. I like it!
However, the solution I'm looking for is "Create a feature whereby the default on charts can be to display the most recent 12 months of data". 

Is this possible, without having to put any segmentation of duration or month? I need something that is by default.

For example. If the data has information up to December, then show me information from Jan/21 to Dec/21 (12 months)

If I have information until Feb/22. So that the calculations are by default from Mar/21 to Feb/22 (12 months)

[MFelix]

Hi @Anonymous ,

When you mean by default the last 12 months of data, you want to show other data if there is another selection? for example if you select 3 years of data then you get the 3 years on your visual, if you don't select anything then you will have the 12 months?

Without any "segmentation" this is not possible by default you need at least to create a measure that will be used to filtered out at visualization level.

Check if this video helps

https://www.youtube.com/watch?v=KBEsPGxDxHM

[Ashish_Mathur]

Thank you for your kind words.  I do not know how to do that.

[MFelix]

Hi @Anonymous ,

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

Regards,

MFelix

[Anonymous]

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.

[MFelix]

Hi @Anonymous ,

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 

[Ashish_Mathur]

Hi,

Whom are you responding to?

[Anonymous]

I responded to @MFelix . But any advise is appreciated 

[Ashish_Mathur]

Hi,

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