Forecasting current month based on previous month - measure

[apple43]

Hi Community,

I want to create a report/table that shows the following information:

. Country
. qty sales (month to date) - measure already exists
. qty sales last month (total month) - measure already exists

. qty sales CM Total (forecast current month based on previous month) - need to create

. variance qty sales (month to date) - need to create

Example below:  Date = 14 Nov

	MTD	October (total)	Forecast November (total)	var CM/LM (MTD)
United States	800	1500	1714	18%
France	220	450	471	8%
Germany	200	500	429	-11%
Spain	175	400	375	-3%

Can you help me?

Thank you!

[v-xianjtan-msft · Accepted solution]

Hi @apple43 

You can create measures with the following DAX:

[MTD] and [October(total)] are measures you've already created.

Forecast November (total) = 
VAR DaysInCurrentMonth = DAY(EOMONTH(TODAY(), 0))
VAR DaysElapsed = DAY(TODAY())
VAR SalesPerDay = [MTD] / DaysElapsed
RETURN
SalesPerDay * DaysInCurrentMonth

CM/LM (MTD) = 
VAR DaysInLastMonth = DAY(EOMONTH(TODAY(), -1))
VAR DaysElapsed = DAY(TODAY())
VAR LM_MTD = [October (total)] * DaysElapsed / DaysInLastMonth
RETURN
[MTD] / LM_MTD - 1

(Since the TODAY() is used in Measure, the result are dynamic and the current screenshot result in 11/15.)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Poojara_D12 · Accepted solution]

Hi @apple43 

To create a report or table like this, you’ll need to add some calculations in your dataset or BI tool. Here’s how to approach creating each measure based on your example. I’ll walk you through the formulas for:

1. Qty Sales CM Total (Forecast Current Month): Using October as a basis, this forecast assumes that the rest of the month will perform similarly to October.
2. Variance Qty Sales (MTD): This measures the Month-to-Date (MTD) percentage difference compared to the Last Month’s MTD.

---

1. Qty Sales CM Total (Forecast for Current Month)

To create this forecast, you want to estimate the total sales for November based on the sales of October. A simple way to forecast is:

For example, if the current date is November 14th:

• Days Elapsed: 14
• Total Days in Month: 30

In DAX (for Power BI), you could write this formula as follows:

Forecast CM Total = 
    DIVIDE([MTD Sales], DAY(TODAY())) * DAY(EOMONTH(TODAY(), 0))

This formula takes the current Month-to-Date (MTD) sales, divides by the current day in the month to get an average per day, then multiplies by the total days in November (using EOMONTH).

2. Variance Qty Sales (MTD)

The MTD variance compares the current MTD sales against the previous month’s MTD sales to show percentage growth or decline.

In DAX, assuming you already have an MTD measure for last month, this would look like:

Variance Qty Sales (MTD) = 
    DIVIDE([MTD Sales] - [MTD Sales Last Month], [MTD Sales Last Month], 0) * 100

Here’s a breakdown of what each column would contain based on your description:

Country Qty Sales MTD (November) Qty Sales Last Month (October Total) Forecast CM Total (November) Var CM/LM (MTD)

United States	800	1500	1714	18%
France	220	450	471	8%
Germany	200	500	429	-11%
Spain	175	400	375	-3%

Each of these columns would use the formulas provided to dynamically update as MTD sales and date changes. You can then use these measures in your table visual for a dynamic view of current sales trends and forecasted totals for the month.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[raqueiros]

Thank you for your help!

Simple approach.

[v-xianjtan-msft · Accepted solution]

Hi @apple43 

You can create measures with the following DAX:

[MTD] and [October(total)] are measures you've already created.

Forecast November (total) = 
VAR DaysInCurrentMonth = DAY(EOMONTH(TODAY(), 0))
VAR DaysElapsed = DAY(TODAY())
VAR SalesPerDay = [MTD] / DaysElapsed
RETURN
SalesPerDay * DaysInCurrentMonth

CM/LM (MTD) = 
VAR DaysInLastMonth = DAY(EOMONTH(TODAY(), -1))
VAR DaysElapsed = DAY(TODAY())
VAR LM_MTD = [October (total)] * DaysElapsed / DaysInLastMonth
RETURN
[MTD] / LM_MTD - 1

(Since the TODAY() is used in Measure, the result are dynamic and the current screenshot result in 11/15.)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[raqueiros]

Thank you for your help. 

Also solution for my problem.