Forecasting current month based on previous month - measure

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)

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