Dax measure for revenue forecasting

[FelipMark]

I'm setting up a daily monitoring dashboard for the current month's revenue:

I have a measurement in DAX that is correct. I took the accumulated revenue from the current month to today + the daily accumulated revenue trend (based on the average daily revenue for the current month)

actual_measure =
VAR CurrentDate = TODAY() // Sets the current date using the TODAY() function
VAR SalesToDate =
    CALCULATE(
        [revenue], // Calculates revenue
        DATESMTD(dCalendar[Date]) // From the start of the current month to today
    )
VAR DailyAverage =
    [acutal_average] // Ensure this measure is correct as explained earlier
VAR FutureDays =
    FILTER(
        ALL(dCalendar), // Removes all filters from dCalendar
        dCalendar[Date] > CurrentDate && dCalendar[Date] <= EOMONTH(CurrentDate, 0) // Selects days in the current month that are after today
    )
VAR FutureSales =
    SUMX(
        FutureDays, // Iterates over each day in FutureDays
        DailyAverage // Sums the Daily Average for each future day
    )
RETURN
    IF(
        MAX(dCalendar[Date]) <= CurrentDate, // Checks if the last date in the calendar is today or before
        SalesToDate, // If true, returns SalesToDate
        SalesToDate + (DailyAverage * (MAX(dCalendar[Date]) - CurrentDate)) // If false, adds predicted sales for remaining days
    )

actual_average =
VAR TotalRevenueToDate = CALCULATE(
    [revenue],
    DATESMTD(dCalendar[Date])  // Calculates revenue from the start of the current month to today
)
VAR CurrentDate = TODAY()  // Sets the current date using the TODAY() function
VAR FirstDayOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)  // Finds the first day of the current month
VAR ElapsedDays = DATEDIFF(FirstDayOfMonth, CurrentDate, DAY) + 1  // Calculates the number of days from the first of the month to today
RETURN
DIVIDE(TotalRevenueToDate, ElapsedDays)  // Divides the total revenue by the number of elapsed days

Now, in the forecast, instead of taking the daily average of the current month, I'm trying to put it based on the average of each day of the week in the last 3 months (Monday, Tuesday, Wednesday...). However, when I replace the daily average of the current month with the average based on the days of the week, the result is incorrect. Can you say why?

new_measure =
VAR CurrentDate = TODAY() // Sets the current date using the TODAY() function
VAR SalesToDate =
    CALCULATE(
        [revenue], // Calculates revenue
        DATESMTD(dCalendar[Date]) // From the start of the current month to today
    )
VAR DailyAverage =
    [new_average] // Ensure this measure is correct as previously explained
VAR FutureDays =
    FILTER(
        ALL(dCalendar), // Removes all filters from dCalendar
        dCalendar[Date] > CurrentDate && dCalendar[Date] <= EOMONTH(CurrentDate, 0) // Selects days in the current month that are after today
    )
VAR FutureSales =
    SUMX(
        FutureDays, // Iterates over each future day
        DailyAverage // Sums the Daily Average revenue for each future day
    )
RETURN
    IF(
        MAX(dCalendar[Date]) <= CurrentDate, // Checks if the last date in the calendar is today or before
        SalesToDate, // If true, returns SalesToDate
        SalesToDate + (DailyAverage * (MAX(dCalendar[Date]) - CurrentDate)) // If false, calculates predicted future sales
    )

new_average =
VAR CurrentDate = TODAY()  // Setting the current date to ensure calculations are always based on the present moment
VAR StartDate = EOMONTH(CurrentDate, -4) + 1  // Start date set to the first day of the month, four months ago
VAR EndDate = CurrentDate  // End date is today
RETURN
SUMX(
    VALUES(dCalendar[Day of Week Abbrev]),  // Iterating over different days of the week
    VAR CurrentDayOfWeek = dCalendar[Day of Week Abbrev]
    VAR TotalRevenue = CALCULATE(
        SUM(fRevenue[TOTAL]),  // Summing revenue for the specified day of the week
        dCalendar[Day of Week Abbrev] = CurrentDayOfWeek,
        dCalendar[Date] >= StartDate,
        dCalendar[Date] <= EndDate
    )
    VAR DaysOfWeek = CALCULATE(
        COUNTROWS(  // Counting the actual days that match the day of the week within the range
            FILTER(
                ALL(dCalendar),
                dCalendar[Day of Week Abbrev] = CurrentDayOfWeek &&
                dCalendar[Date] >= StartDate &&
                dCalendar[Date] <= EndDate
            )
        )
    )
    RETURN
    DIVIDE(TotalRevenue, DaysOfWeek, BLANK())  // Calculating the average
)

The correct thing to do in "new_measure" was to add up daily on future days based on "new_average" until the end of the current month

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[FelipMark]

Sample data: https://drive.google.com/file/d/177IfnSN-GhFZTkHY8cdepVhHDGgO7lic/view?usp=sharing 

[lbendlin]

Here's the built-in trend line and  forecast cone for your sample data

You can also consider using LINESTX to get the trend line for the linear regression.  Or you could do a sliding window.

You said you wanted to use averages of weekdays for the last three months.  I don't see that in your sample data. It may also not help much.  Better to have SAMEPERODLASTYEAR data.

[FelipMark]

I just want that from today's date, the next dates are calculated like this:

new_measure + new_average = accumulated... and so on


Note: note that in actual_measure this occurs correctly with actual_average

[lbendlin]

I don't see sales data for the last four months in your sample data so your measures cannot be properly tested. You also have intermediate steps in your measures that are then no longer used.  These will not be computed.

I would suggest you refactor your measures and simplify them.

[FelipMark]

uptaded sample data with sales since 01/01/2024: https://drive.google.com/file/d/177IfnSN-GhFZTkHY8cdepVhHDGgO7lic/view?usp=sharing 

the average measurement by day of the week is already bringing the correct results, my problem is to calculate the values ​​of future days based on this average ("new_measure").

the "actual_measure", calculates the future values ​​based on the daily average of the current month correctly, but when trying to evolve and search based on the day of the week, I am encountering this obstacle

[lbendlin]

OK.  What's your question?  Have you considered using the built-in forecasting option for the line chart?

[FelipMark]

I am unable to make the new measure, starting from today's date, individually add up each day accumulated until the end of the month, as you can see in the last image.

The correct thing would be that, from the 20th onwards, the "new_measure" column is accumulated based on the "new_average" column.


The "new_average" measure makes more sense for calculating future values than built-in forecasting option for the line chart.