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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
gluizqueiroz
Resolver I
Resolver I

How to create a measure that "predict" some values based in a average values from previous months?

I have a simple table with some sales day per day named Fact_Sales and a simple and normal calendar table named Dim_Calendar.
And I have a measure that is a simple SUM(Fact_Sales[Sales]) and a measure that calculate the % variation of sales month over month.

 

If I put in a visual table the column Dim_Calendar[Month/Year] and the measure SUM(Fact_Sales[Sales]) and the measure % Variation,we will see the months and the total of sales in this months and the % month over month, right?

 

I need to create a measure that calculate an AVERAGE value of last 12 months % Variaton, and use this average to predict the next 12 months of Sales, using the sales of last month.

 

With the following example, it becomes more clear to understand.

 

gluizqueiroz_1-1676954386556.png

In column D, you can see the formula to calculate the value in blue (column C).

There is an curious fact, the months mar/23 until jan/24 doesn't exists in my table Dim_Calendar, feb/22 exists, but we need to predict too because the month is not complete yet.
We use Sales of jan/23 because it is the MAX complete month until today.

I need to use this measure in a simple card to show the value $ 52.280,18 and it need to be a MEASURE, because I have some slicers and filters in my page and the values must accept the filters, it cant be a fixed table in Power Query or something.

 

1 REPLY 1
BrianConnelly
Resolver III
Resolver III

Ill follow up tomorrow when at work, I did this.   But to get you started, you need to first get the average by summarizing the variation by month.

Measure = 

VAR summaryTable = SUMMARIZECOLUMNS(Month, "Variation", {measure you created])

RETURN AVERAGEX(summaryTable,[Variation])

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.