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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rolling 12 Month Trend Line when selecting a month

So I am wanting to create a Trend Chart that will show a rolling 12 month trend line.  If I select Jan. 2019 as a slicer I want it to show from last January to this one in the trend.  

 

I have been trying to use the below link but this doesn't seem to be working:

https://community.powerbi.com/t5/Desktop/Possible-for-a-line-chart-to-show-a-rolling-12-months-where...

 

I tried this DAX calculation but all of the #'s are coming through the same no matter what month I look at...

 

Full Time Hires in last 12 month = CALCULATE(SUMX(FactHires, FactHires[FullTimeNbr]), DATESBETWEEN(DIMCalendar[DateKey], EDATE(TODAY(), -12), TODAY()))
 
 
2 ACCEPTED SOLUTIONS
blopez11
Resident Rockstar
Resident Rockstar

I was able to accomplish similar to what you are wanting using the fine article below (has a working example as well)

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

Good luck,

View solution in original post

You'll have to specify another coumn to sort your month name by, say a numerical MonthOfYear field populated with 1 through 12 depending on the month name for the row

 

You can do this in either the "Report" or "Data" view, by selecting the table then the field you are using in those visualizations, then in the Modeling tab select "Sort by Column" to specify your sort column

 

View solution in original post

4 REPLIES 4
blopez11
Resident Rockstar
Resident Rockstar

I was able to accomplish similar to what you are wanting using the fine article below (has a working example as well)

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

Good luck,

Anonymous
Not applicable

Thanks, I actually had found that after I posted this!

 

One stupid question, how do I get the chart and the drop down box to sort in monthly order?  Right now they both go in alphabetical order...

 

 

You'll have to specify another coumn to sort your month name by, say a numerical MonthOfYear field populated with 1 through 12 depending on the month name for the row

 

You can do this in either the "Report" or "Data" view, by selecting the table then the field you are using in those visualizations, then in the Modeling tab select "Sort by Column" to specify your sort column

 

Anonymous
Not applicable

Ok, thanks, both of you guys have gotten me to get the 12 month rolling Trend to work.  But I do have another question.  Not sure if thsi should be a new post or not.  

 

One of the measures I want to have as a rolling 12 month measure is a calculation.  I'm not sure how to break this up, or do this.  The calculation is a Percentage calculation.  That goes something like this:

 

Percent = ((Rolling 3 month T sum) * 12 / 4)/  (Rolling 3 month H sum)

 

Currently I have a measure that calculates the 3 month t sum and the 3 month H sum.  Although I'm not sure how to pull this into the calculation they have in the above link...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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