DAX Power BI: EOMONTH — End of Month Sales Forecast

We need to create a measure that calculates the sales forecast by the end of the month. In order to make forecasts, we will divide the formula into 2 parts: the first one calculates sales from the first day to the current day, that is, we count the accumulated sales for the month, and the second part — calculates sales for the future period, that is, from the current day to the last. And these last sales are calculated based on the weighted average sales of the last 30 days.

To create this measure, we will need the EOMONTH and EDATE functions.

The EOMONTH function, like the FIRSTDATE and LASTDATE functions, returns the last date in a month, but that particular month we can specify. The EDATE function returns the date that will be moved to a specific period from the current date.

Let’s look at our data model

Let’s start writing the formula. We will use the CALCULATE function to do this.

The first value is TotalSum

The second argument is a filter that limits the last 30 days

But it is more reliable to use the FILTER function, and there, before taking the last 30 days, to clear the table of all filters with the ALL function

We can see that the measure counts correctly if we set the slice to the last 30 days and output the TotalSum measure.

Once we have the value for the last 30 days we need to calculate the average value. You can do this by dividing the sum of all values by 30. But that’s how an ordinary average is calculated, and we need a weighted average.

To do this, we add the sales of the last 30 days to the sales of the last 7 days. And now we divide all this not by 30, but by 37. So we get the weighted average of the last 30 days of sales, which gives us a closer to the truth.

Let’s complicate this formula by adding several intervals and adding variables in parallel

Next, to get a forecast — how many sales we will create from the current day to the last day, we need to multiply the average number of sales for 1 day by the remaining days, that is, how many days remain until the last day of the month. The number of days remaining can be calculated using the DATEDIFF function. We put the start date there, and after the EOMONTH function value, TODAY is fed to it as a start value, and the second parameter is how many months we need to move our months. In our case, this parameter is zero. So EOMONTH will return the date of the last day of that month.

As a result, the expression we described above will give us the projected amount of sales from today to the last day of the month. But, to get the final prediction, we need to add to this predicted amount of sales the actual amount of sales that we made from the first date to today. We can do that with the TOTALMTD function.

Let’s put it all together and get a measure of

PredictTotal = 
VAR Total90days = 
    CALCULATE(
        [TotalSum],
        FILTER(
            ALL('Orders'),
            'Orders'[Date] > TODAY() - 90
        )
    )
VAR Total30days = 
    CALCULATE(
        [TotalSum],
        FILTER(
            ALL('Orders'),
            'Orders'[Date] > TODAY() - 30
        )
    )
VAR Total14days = 
    CALCULATE(
        [TotalSum],
        FILTER(
            ALL('Orders'),
            'Orders'[Date] > TODAY() - 14
        )
    )    
VAR Total7days = 
    CALCULATE(
        [TotalSum],
        FILTER(
            ALL('Orders'),
            'Orders'[Date] > TODAY() - 7
        )
    )
VAR DayToMonthEnd = DATEDIFF(TODAY(), EOMONTH(TODAY(), 0), DAY) /// count of days to end of month
VAR TotalFromStartMonth = TOTALMTD([TotalSum], 'Calendar'[Date])
RETURN
    (Total30days + Total7days) / (90 + 30 + 14 + 7) /// Average sum for 30-days
    * 
    DayToMonthEnd
    + 
    TotalFromStartMonth

It is important to understand that in order to use this measure it is necessary to display it exactly in a card format