DAX measure for monthly outturn

[DCW]

Hopefully a simple one..... I'm trying to create a DAX measure for a full month out-turn, with the following critera.  My fact table has revenue at a daily level.

1. Take actuals for completed days of the month.

2. Use a forecast for remaining days of the month, based on the average of the 5 previous same weekdays.

So if I was running today (3rd June), it would take actuals from 1st & 2nd June as completed, and then for 3rd June (Tuesday), it would take an average of the previous 5 Tuesdays, for 4th June (Wednesday), an average of previous 5 Wednesdays, and onwards etc.

Add 1+2 for expected full month outturn!

[v-sshirivolu · Accepted solution]

Hi @DCW ,
Thanks for reaching out to the Microsoft fabric community forum.

Please try the following DAX measures :

ActualsToDate :=
CALCULATE (
SUM (Revenue[Amount]),
FILTER (
ALL (Calendar),
Calendar[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
&& Calendar[Date] < TODAY()
)
)


ForecastRemaining :=
SUMX (
FILTER (
ADDCOLUMNS (
CALENDAR ( TODAY(), EOMONTH ( TODAY(), 0 ) ),
"Weekday", WEEKDAY ( [Date], 2 )
),
[Date] >= TODAY()
),
VAR ForecastDate = [Date]
VAR WeekdayNum = WEEKDAY(ForecastDate, 2)
VAR Last5SameWeekdays =
TOPN (
5,
FILTER (
ALL (Calendar),
Calendar[Date] < ForecastDate
&& WEEKDAY(Calendar[Date], 2) = WeekdayNum
),
Calendar[Date], DESC
)
VAR AvgAmount =
AVERAGEX (
FILTER (
Revenue,
Revenue[Date] IN SELECTCOLUMNS ( Last5SameWeekdays, "Date", Calendar[Date] )
),
Revenue[Amount]
)
RETURN
AvgAmount
)


MonthlyOutturn :=
[ActualsToDate] + [ForecastRemaining]

Find attached pbix file for your reference

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[v-sshirivolu]

Hi @DCW  ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solutionand give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

[v-sshirivolu]

Hi @DCW  ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solutionand give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

[v-sshirivolu]

Hi @DCW 

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

[techies]

Hi @DCW please try this

Full Month Outturn =

VAR TodayDate = TODAY()

VAR EndOfMonthDate = EOMONTH(TodayDate, 0)

VAR ActualsToDate =

    CALCULATE (

        SUM ( FactRevenue[Revenue] ),

        FILTER (

            'DateTable',

            'DateTable'[Date] < TodayDate &&

            MONTH ( 'DateTable'[Date] ) = MONTH ( TodayDate )

        )

    )

VAR RemainingDates =

    FILTER (

        ALL ( 'DateTable' ),

        'DateTable'[Date] >= TodayDate &&

        'DateTable'[Date] <= EndOfMonthDate

    )

VAR Forecast =

    SUMX (

        RemainingDates,

        VAR CurrentDate = 'DateTable'[Date]

        VAR WeekdayName = 'DateTable'[Weekday Name]

        VAR PriorSameWeekdays =

            TOPN (

                5,

                FILTER (

                    ALL ( 'DateTable' ),

                    'DateTable'[Weekday Name] = WeekdayName &&

                    'DateTable'[Date] < CurrentDate

                ),

                'DateTable'[Date], DESC

            )

        VAR AvgRevenue =

            AVERAGEX (

                PriorSameWeekdays,

                CALCULATE (

                    SUM ( FactRevenue[Revenue] ),

                    FILTER (

                        ALL ( FactRevenue ),

                        FactRevenue[Date] = 'DateTable'[Date]

                    )

                )

            )

        RETURN

            AvgRevenue

    )

RETURN

ActualsToDate + Forecast

[Jihwan_Kim]

Hi, I am not sure if I understood your question correctly, but please check the below measure and the attached pbix file.
I assume the logic is always looking at before-today, even the date is 30th June, for instance.
I tried to use WINDOW Dax function in the measure.

WINDOW function (DAX) - DAX | Microsoft Learn

Revenue: = 
VAR _rowdate =
    MAX ( calendar_dim[Date] )
VAR _today =
    TODAY ()
VAR _rowmonth =
    MAX ( calendar_dim[Year-Month sort] )
VAR _todaymonth =
    EOMONTH ( _today, 0 )
VAR _samemonthfuture = _rowmonth = _todaymonth
VAR _rowdayname =
    MAX ( calendar_dim[Day name] )
VAR _t =
    FILTER (
        ADDCOLUMNS (
            FILTER ( ALL ( calendar_dim ), calendar_dim[Day name] = _rowdayname ),
            "@previousrevenue", CALCULATE ( SUM ( revenue_fact[revenue] ) )
        ),
        [@previousrevenue] <> BLANK ()
    )
VAR _recentfiveavg =
    AVERAGEX (
        WINDOW ( 1, ABS, 5, ABS, _t, ORDERBY ( calendar_dim[Date], DESC ) ),
        [@previousrevenue]
    )
RETURN
    SWITCH (
        TRUE (),
        _rowdate < _today, SUM ( revenue_fact[revenue] ),
        _samemonthfuture, _recentfiveavg
    )

[SamsonTruong]

Hi @DCW 

Please try the following DAX measure and let me know if it achieves the result you are looking for. Some assumptions made are that you have a date table connected to your fact table. Here is the DAX measure:

Monthly Outturn = 
VAR TodayDate = TODAY()
VAR EndOfMonth = EOMONTH(TodayDate, 0)

VAR ActualsToDate =
    CALCULATE(
        SUM('RevenueFact'[RevenueAmount]),
        FILTER(
            'Date',
            'Date'[Date] <= TodayDate &&
            MONTH('Date'[Date]) = MONTH(TodayDate) &&
            YEAR('Date'[Date]) = YEAR(TodayDate)
        )
    )

VAR RemainingDates =
    FILTER(
        'Date',
        'Date'[Date] > TodayDate &&
        'Date'[Date] <= EndOfMonth &&
        MONTH('Date'[Date]) = MONTH(TodayDate) &&
        YEAR('Date'[Date]) = YEAR(TodayDate)
    )

VAR ForecastRemaining =
    SUMX(
        RemainingDates,
        VAR CurrentWeekday = WEEKDAY('Date'[Date], 2)
        VAR HistoricalDates =
            TOPN(
                5,
                FILTER(
                    ALL('Date'),
                    WEEKDAY('Date'[Date], 2) = CurrentWeekday &&
                    'Date'[Date] < TodayDate
                ),
                'Date'[Date], DESC
            )
        VAR AvgRevenue =
            AVERAGEX(
                HistoricalDates,
                CALCULATE(SUM('RevenueFact'[RevenueAmount]))
            )
        RETURN AvgRevenue
    )

RETURN
ActualsToDate + ForecastRemaining

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson