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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
puru85
Helper II
Helper II

Adding Last 12 Months Average Sales as Constant Trend Line in Line Chart

Hi Experts,

I am calculating the Last 12 Months Average Sales as a Constant Trend Line.

Line Chart:
X Axis: Date Dimension
Y Axis: Sum of Sales

 

puru85_0-1721217614565.png

Requirement:

  • I need to add a trendline that represents the "Last 12 Months Average Sales."
  • For the trendline, based on the year filtered (2024) and month filtered (July), the Last 12 Months Average Sales is $2008.00. You can refer to the attached Excel file for the Last 12 Months Average Sales calculation.

Average Sales Past 12 Month.xlsx

I need to add a constant trend line in the line chart similar to the illustrated image provided.

puru85_1-1721218012830.png

 

I have also attached a PBIX file for your reference.


12Months-AverageSalesTrendLine.pbix

Anyone, Please Help.

Thank you!


 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@puru85 , Last 12 month Avg, based on max selected date, you need a measure like below

 

12 Month Avg =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],_max,-12,MONTH))

 

Make sure you are using a date table connected with the date of your table. And field from the date table is used in Slicer, visual, and Measure

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@puru85 , Last 12 month Avg, based on max selected date, you need a measure like below

 

12 Month Avg =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],_max,-12,MONTH))

 

Make sure you are using a date table connected with the date of your table. And field from the date table is used in Slicer, visual, and Measure

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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