Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Moving X axis based on Month selected

I am trying to create a line chart that varies with slicer selected. Let me explain briefly

 

I have created measures as follows: 

Period Start Dates_Dim = FIRSTDATE( DATESINPERIOD(Dates_Dim[Date], [Period End Dates_dim], -3, MONTH))
Period End Dates_dim = LASTDATE(Dates_Dim[Date])
R12 Period Start Dates_Dim = FIRSTDATE( DATESINPERIOD(Dates_Dim[Date], [Period End Dates_dim], -12, MONTH))
 
Rolling 12 Month Average Dates_Dim = divide(CALCULATE(SUM(Oracle_Order_Management[Sales]),DATESBETWEEN ( Dates_Dim[Date], [R12 Period Start Dates_Dim], [Period End Dates_dim] )),12,0)
 
Rolling 3 Month Average Dates_Dim = divide(CALCULATE(SUM(Oracle_Order_Management[4WE Quantity]),DATESBETWEEN ( Dates_Dim[Date], [Period Start Dates_Dim], [Period End Dates_dim] )),3,0)
 
Now in Line chart i have added Month_Name as Axis, Products in Legend and Rolling 3 Month Average Dates_Dim as values.
------------------------------------------------------------------------------------------------------------------------------------------
 
But what i need is i need to display rolling 3 months avg or rolling 12 months average based on Slicer. Even that i was able to achieve. Now next hurdle is making the X axis change dynamically with month selected. If i select march i should see average as jan+feb+mar. So i need everyhting to work together, R3M VS R12M slicer along with Month Year Slicer 

I can provide sample PBIX file if that would make life easy.
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try like this.

    These formulae will change based on the slicer. the date is date dimension here.

     

     Sales 12 month = ( 
VAR _Cuur_start = Maxx('Date',dateadd('Date'[Date],-12,MONTH)) 
VAR _Curr_END = Max('Date'[Date])
return 
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
)

Sales 3 month = ( 
VAR _Cuur_start = Maxx('Date',dateadd('Date'[Date],-3,MONTH)) 
VAR _Curr_END = Max('Date'[Date])
return 
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
)

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak Did not work. When i selected august the line chart shows values as dots, where as i wanted the line chart to display march to august in X axis and every month show 3 months average

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Share some data and show the expected result.  As of now, don't bother about the visual.  For the data that you share, just show your actual expected result in a Table.  Once the numbers in the Table appear OK, we can alsoways switch to any visual.