Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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.
3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

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

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

@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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.