Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help on dynamic axis based on slicer value

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!

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks. will check this.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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.