topic Re: DAX measure for monthly outturn in DAX Commands and Tips

DAX measure for monthly outturn

DCW — Tue, 03 Jun 2025 15:33:02 GMT

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!

Re: DAX measure for monthly outturn

SamsonTruong — Tue, 03 Jun 2025 16:07:55 GMT

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

Re: DAX measure for monthly outturn

Jihwan_Kim — Tue, 03 Jun 2025 17:03:29 GMT

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 )

Re: DAX measure for monthly outturn

techies — Tue, 03 Jun 2025 19:09:16 GMT

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 (

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

Re: DAX measure for monthly outturn

Anonymous — Wed, 04 Jun 2025 06:20:26 GMT

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

Re: DAX measure for monthly outturn

Anonymous — Mon, 09 Jun 2025 05:53:30 GMT

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.

Re: DAX measure for monthly outturn

Anonymous — Thu, 12 Jun 2025 09:48:57 GMT

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.

Re: DAX measure for monthly outturn

Anonymous — Mon, 16 Jun 2025 14:32:44 GMT

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.