Help on dynamic axis based on slicer value

[Anonymous]

Hi. Kindly asking for help: I have a chart with x-axis as months. I want to show only 6 months worth of records here but it should be dependent on the selected slicer. ex: Selected on slicer is Sep'19 the chart should show Apr'19-Sep'19; but when we select Aug'19 on the slicer chart axis to show Mar'19-Aug'19. Hope you can assist. Thanks!

[V-lianl-msft · Accepted solution]

Hi @Anonymous,

You may refer to the post below to create a disconnected table for your slicer and then create a measure based on that selection.

https://community.powerbi.com/t5/Desktop/Dynamically-changing-Months-based-on-X-axis-of-line-chart-based/m-p/823754 

Best Regards,

Liang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

First you should create a calendar table and use it as a slicer, then create a measure.

1. calendar table

DimDate = ADDCOLUMNS(CALENDAR("01/01/2018","31/12/2019"),"Month - Year", FORMAT([Date],"mm - yy"),"SortMonth",FORMAT([Date],"yyyymm"))

2.use it as a slicer

3. create a measure

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

Here is the .pbix

Best Regards,

Liang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks. Will surely try this.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[V-lianl-msft · Accepted solution]

Hi @Anonymous,

You may refer to the post below to create a disconnected table for your slicer and then create a measure based on that selection.

https://community.powerbi.com/t5/Desktop/Dynamically-changing-Months-based-on-X-axis-of-line-chart-based/m-p/823754 

Best Regards,

Liang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks. will check this.