topic Re: Forecasting current month based on previous month - measure in DAX Commands and Tips

Forecasting current month based on previous month - measure

apple43 — Thu, 14 Nov 2024 18:20:45 GMT

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!

Re: Forecasting current month based on previous month - measure

Anonymous — Fri, 15 Nov 2024 02:07:48 GMT

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.

Re: Forecasting current month based on previous month - measure

Poojara_D12 — Fri, 15 Nov 2024 04:41:01 GMT

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:

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.
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

Re: Forecasting current month based on previous month - measure

raqueiros — Tue, 19 Nov 2024 12:28:23 GMT

Thank you for your help!

Simple approach.

Re: Forecasting current month based on previous month - measure

raqueiros — Tue, 19 Nov 2024 12:29:09 GMT

Thank you for your help.

Also solution for my problem.