Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic X axis via slicer

hi

i want to change my x axis dynamically via slicer(i.e week,month,year,date) 

 

Last 7Days Sales Avg= CALCULATE(AVERAGEX(Sales,Sales[Detail.qty]), DATESINPERIOD('Calendar'[Date], TODAY(),-7,DAY)
Total Sales = sum(Sales[Detail.qty])
 
Please suggest me how to modify my dax to get my requirement or suggest me some other approach.
 
 
Thanks
 
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We think use the hierarchy will be more flixable than slicer. We can create a hierarchy by the level "Year-Week-Day" (Drap one field to another field), use it as the x-axis field, then chang your measure to following:

     

    Last X Days Sales Avg =
VAR day =
    IF (
        ISINSCOPE ( 'Calendar'[Year] ),
        -365,
        IF ( ISINSCOPE ( 'Calendar'[Week] ), -7, -1 )
    )
RETURN
    CALCULATE (
        AVERAGEX ( Sales, Sales[Detail.qty] ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), day, DAY )
    )

    Then  we can use the drilldown and drillup feature (in the top of your chart) to meet your requirement. But the measure name cannot be dynamic.

     

     

     

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    We think use the hierarchy will be more flixable than slicer. We can create a hierarchy by the level "Year-Week-Day" (Drap one field to another field), use it as the x-axis field, then chang your measure to following:

     

    Last X Days Sales Avg =
VAR day =
    IF (
        ISINSCOPE ( 'Calendar'[Year] ),
        -365,
        IF ( ISINSCOPE ( 'Calendar'[Week] ), -7, -1 )
    )
RETURN
    CALCULATE (
        AVERAGEX ( Sales, Sales[Detail.qty] ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), day, DAY )
    )

    Then  we can use the drilldown and drillup feature (in the top of your chart) to meet your requirement. But the measure name cannot be dynamic.

     

     

     

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

      v-lid-msft wrote:

      Hi Anonymous ,

       

      We think use the hierarchy will be more flixable than slicer. We can create a hierarchy by the level "Year-Week-Day" (Drap one field to another field), use it as the x-axis field, then chang your measure to following:

       

      Last X Days Sales Avg =
VAR day =
    IF (
        ISINSCOPE ( 'Calendar'[Year] ),
        -365,
        IF ( ISINSCOPE ( 'Calendar'[Week] ), -7, -1 )
    )
RETURN
    CALCULATE (
        AVERAGEX ( Sales, Sales[Detail.qty] ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), day, DAY )
    )

      Then  we can use the drilldown and drillup feature (in the top of your chart) to meet your requirement. But the measure name cannot be dynamic.

       

       

       

       

      BTW, pbix as attached.

       

      Best regards,

      Community Support Team _ Dong Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      v-lid-msft wrote:

      Hi Anonymous ,

       

      We think use the hierarchy will be more flixable than slicer. We can create a hierarchy by the level "Year-Week-Day" (Drap one field to another field), use it as the x-axis field, then chang your measure to following:

       

      Last X Days Sales Avg =
VAR day =
    IF (
        ISINSCOPE ( 'Calendar'[Year] ),
        -365,
        IF ( ISINSCOPE ( 'Calendar'[Week] ), -7, -1 )
    )
RETURN
    CALCULATE (
        AVERAGEX ( Sales, Sales[Detail.qty] ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), day, DAY )
    )

      Then  we can use the drilldown and drillup feature (in the top of your chart) to meet your requirement. But the measure name cannot be dynamic.

       

       

       

       

      BTW, pbix as attached.

       

      Best regards,

      Community Support Team _ Dong Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      pbix link has expired. could you please again share pbix file.