Sales forecast based on historical data

Hi eliseglines​ 

Thanks for posting your question.

To better understand the forecasting requirement and provide an accurate Power BI/DAX solution, could you please provide:

• Sample data covering the scenario, in a usable format rather than a screenshot.
• The expected output based on the sample data.
• Any existing DAX measure or approach you have already tried, if applicable.

Please ensure that the sample data does not contain any sensitive or confidential information and only includes the fields relevant to this issue.

This will help us understand the required calculation and validate the expected result without making assumptions about your data model.

How to provide sample data in the Power BI Forum | Microsoft Fabric Community

Thank you ShivekMaharaj​ SO much for your help! I am now only having one snag (I think minor, but can't figure it out). For some reason my previous quarter sales is the same as the total sales amount. (I summed the total amount field to confirm the total sales calculation was correct in the table below.) And I did confirm in my excel data that this $63,038 amount for this company is correct.

With the previous quarter and total sales amount being equal - this obviously makes my QoQ growth % =0. Any ideas why the previous quarter sales is defaulting to the total amount? (The invoice quarter is something I wrote into my excel file to summarize the Quarter given the invoice date).

Silly question - do I need to link the date table to my other table? Or is this not an issue because I saved it as a date table?

I used this equation:

Previous Quarter Sales =

CALCULATE(

[Total Sales],

DATEADD('Date'[Date], -1, QUARTER)

)

Thanks so much for everything!

Yes,this is very doable in Power BI with DAX. The key is to use a proper Date/Quarter table rather than trying to calculate the quarter directly from the sales table.

Assume:

Sales[Amount] = sales amount

Sales[PaidDate] = payment date

'Date'[Date] = calendar date

'Date'[Quarter] = quarter

'Date'[QuarterIndex] = sequential quarter number

1. Base measure

Total Sales =

SUM(Sales[Amount])

2. Average sales for the latest 3 quarters

Avg Last 3 Quarters =

AVERAGEX(

TOPN(

3,

VALUES('Date'[QuarterIndex]),

'Date'[QuarterIndex], DESC

),

CALCULATE([Total Sales])

)

3. Calculate quarter-over-quarter growth

QoQ Growth =

DIVIDE(

[Total Sales] - CALCULATE(

[Total Sales],

DATEADD('Date'[Date], -1, QUARTER)

),

CALCULATE(

[Total Sales],

DATEADD('Date'[Date], -1, QUARTER)

)

)

4. Average growth of the latest 3 quarters

You can then average the QoQ growth rates and apply that growth to the latest quarter:

Forecast Next Quarter =

VAR LatestQuarter =

CALCULATE(

[Total Sales],

LASTDATE('Date'[Date])

)

VAR AvgGrowth =

AVERAGEX(

TOPN(

3,

VALUES('Date'[QuarterIndex]),

'Date'[QuarterIndex], DESC

),

[QoQ Growth]

)

RETURN

LatestQuarter * (1 + AvgGrowth)

Important

If you want this to work correctly when filtering by Company, make sure Company filters your Sales table through the data model. The measures above will then automatically calculate the forecast separately for the selected company.

I'd recommend using a proper Date table with a unique QuarterIndex rather than columns such as "Q1 2025" alone. That avoids many of the DAX errors you're probably encountering.

Hi eliseglines​ ,

The issue was that Previous Quarter Sales was returning the same value as Total Sales, causing QoQ Growth to show 0%. The root cause was that the Date table was not actively related to Sales Report Detail[Invoice Date]; simply marking the table as a Date table does not create a relationship. The fix was to create an active 1-to-many relationship from Date[Date] → Sales Report Detail[Invoice Date] and use Date[Year Quarter] in the visual. With that relationship in place, DATEADD('Date'[Date], -1, QUARTER) correctly shifts the filter context to the previous quarter, allowing Previous Quarter Sales and QoQ Growth to calculate correctly.

I've attached a sample .pbix with solution for your reference.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUserand Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

Hi eliseglines​ 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

Hi eliseglines​,

I've recreated .pbix file for you with your complete requirement. 

Kindly go through it and let me know if anything else is required.

Below is the screenshot from the report. 

VAR CurrentQuarter = SUM('Sales Report Detail'[Total Amount]) VAR PrevQuarter = CALCULATE( SUM('Sales Report Detail'[Total Amount]), DATEADD('Sales Report Detail'[Invoice Date Month], -3, MONTH) ) RETURN DIVIDE(CurrentQuarter - PrevQuarter, PrevQuarter, 0)

Company C - 5/1/2025 invoice date, $500 sales, May, Quarter 2, 2025
8/1/2025, invoice date, $300 sales, August, Quarter 3, 2025
10/2/2025 invoice date, $130 sales, October, Quarter 4, 2025

1/1/2026 invoice date, $70 sales, January, Quarter 1, 2026 

Trying to look at rate change between quarters and use the average of this rate change to add/subtract to most recent quarter data to project Quarter 2 2026

It won't accept "divide" as a function. But I'm not sure how else to run it. I'm obviously doing something or lots of things incorrectly!

Hi eliseglines​,

Thanks for posting the sample and the DAX - that makes the requirement much clearer.

Your use of DIVIDE() is actually fine. Microsoft documents the DIVIDE function as:

DIVIDE(numerator, denominator [, alternateResult])

so this part is valid:

DIVIDE(CurrentQuarter - PrevQuarter, PrevQuarter, 0)

The part I would change is the date calculation.

For quarter-over-quarter calculations, I would create a proper Date table, relate Date[Date] to your invoice date, and use that Date table for the time-intelligence calculation. Microsoft's date-table guidance recommends a continuous date table for this type of calculation.

Your base measures could then be:

Total Sales = SUM('Sales Report Detail'[Total Amount])

and:

Previous Quarter Sales =
CALCULATE(
[Total Sales],
DATEADD('Date'[Date], -1, QUARTER)
)

Microsoft's DATEADD documentation supports shifting a date context directly by QUARTER, so I would use -1, QUARTER rather than -3, MONTH.

Your quarter-over-quarter percentage measure can then be:

QoQ Growth % =
DIVIDE(
[Total Sales] - [Previous Quarter Sales],
[Previous Quarter Sales]
)

For your sample data that gives approximately:

• Q3 2025 vs Q2 2025: -40.00%
• Q4 2025 vs Q3 2025: -56.67%
• Q1 2026 vs Q4 2025: -46.15%

The average of those three changes is approximately -47.61%, so applying that average to the latest quarter:

$70 × (1 - 47.61%) ≈ $36.68

would give you a simple forecast for Q2 2026.

If you want Power BI to calculate that forecast automatically, I would also add a Quarter Start column to the Date table:

Quarter Start =
DATE(
YEAR('Date'[Date]),
3 * INT((MONTH('Date'[Date]) - 1) / 3) + 1,
1
)

Then a measure for the next-quarter forecast based on the latest three quarter-over-quarter changes can be written like this:

Forecast Next Quarter =
VAR LatestDate =
CALCULATE(
MAX('Sales Report Detail'[Invoice Date]),
REMOVEFILTERS('Date')
)

VAR LatestQuarter =
DATE(
YEAR(LatestDate),
3 * INT((MONTH(LatestDate) - 1) / 3) + 1,
1
)

VAR Q1 = LatestQuarter
VAR Q2 = EDATE(Q1, -3)
VAR Q3 = EDATE(Q1, -6)
VAR Q4 = EDATE(Q1, -9)

VAR SalesQ1 = CALCULATE([Total Sales], REMOVEFILTERS('Date'), 'Date'[Quarter Start] = Q1)
VAR SalesQ2 = CALCULATE([Total Sales], REMOVEFILTERS('Date'), 'Date'[Quarter Start] = Q2)
VAR SalesQ3 = CALCULATE([Total Sales], REMOVEFILTERS('Date'), 'Date'[Quarter Start] = Q3)
VAR SalesQ4 = CALCULATE([Total Sales], REMOVEFILTERS('Date'), 'Date'[Quarter Start] = Q4)

VAR Growth1 = DIVIDE(SalesQ1 - SalesQ2, SalesQ2)
VAR Growth2 = DIVIDE(SalesQ2 - SalesQ3, SalesQ3)
VAR Growth3 = DIVIDE(SalesQ3 - SalesQ4, SalesQ4)
VAR AvgGrowth = AVERAGEX({Growth1, Growth2, Growth3}, [Value])

RETURN

SalesQ1 * (1 + AvgGrowth)

That measure will also preserve the current company filter context, so if you put Company on rows, each company can receive its own forecast based on its recent quarterly history.

I would get the basic Total Sales, Previous Quarter Sales, and QoQ Growth % measures working first, then add the forecast measure.

One modelling note: with only three growth observations, this type of forecast can move quite sharply. The DAX can calculate exactly what you described, but once it is working I would validate whether three quarters is enough history for the business forecast you want.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

v-aatheeque​ I changed to make the prediction based on the average of the past 4 quarters (vs 3). I noticed when I pulled this value into the table, it is the same for each quarter:

Maybe my error is somewhere that is in the last forecast column? I would think this should change just like the average of the last 4 quarters field is changing?