topic Re: Self-reference with DAX for Average in DAX Commands and Tips

Self-reference with DAX for Average

Ldomal — Tue, 26 Nov 2024 03:00:35 GMT

Hi Everyone, I have an Issue currently I have a measure called [Rate] which will be provided for actual calculated months, what I need to avhive in the [Average] Column is if the [Rate] has a value keep the rate otherwise provide an average of the last 12 months but e.g. in the table provided Nov will be the average of Nov 23 to Nov 24 [Rates] but for December I will need to take the recently calculated rate for Nov 24 and consider it for the Avg Dec 23 to Dec 24 my main issue is power BI is not self referencing the latest calculated row inted is only taking the moving average until Nov 24.

Here is the Dax I'm using:

DEFINE

var _RollingPeriodStart = DATE(2024,3,1)

var _Start = DATE(2023,1,1)

var _PreviousMonth = DATE(2025,5,31)

var _LastSync = [Latest Sync Date]

var _Table =

SUMMARIZE(

FILTER(

ALL('Calendar'),

'Calendar'[Date] >= _Start &&

'Calendar'[Date] <= _PreviousMonth

'Calendar'[Year-Month],

'Calendar'[Year Month Class],

"EoMDate", LASTDATE('Calendar'[Date]),

"Rate", [_Rate] * 100

)

var _Table_2 =

ADDCOLUMNS(

_Table,

"Index", RANKX(

_Table,

'Calendar'[Year Month Class],

ASC,

DENSE

)

var _Table_3 =

ADDCOLUMNS(

_Table_2,

"Average",

IF(

NOT(ISBLANK([Rate])),

[Rate],

AVERAGEX(

FILTER(

_Table_2,

[Index] < EARLIER([Index]) &&

[Index] >= (EARLIER([Index]) - 12)), [Rate])))

EVALUATE

_Table_3

This what need to be achieved.

Instead Power BI is doing this

Re: Self-reference with DAX for Average

Kedar_Pande — Tue, 26 Nov 2024 06:23:59 GMT

Ldomal

Create the Measure

Average = 
VAR CurrentMonth = MAX('YourTable'[Calendar/Year-Month]) 

VAR CurrentRate = [Rate] 

VAR LastRate =
CALCULATE(
LASTNONBLANK('YourTable'[Rate], 1),
PREVIOUSMONTH('YourTable'[Date]) 
)

RETURN
IF(
NOT(ISBLANK(CurrentRate)),
CurrentRate,
IF(
NOT(ISBLANK(LastRate)),
AVERAGEX(
DATESINPERIOD(
'YourTable'[Date],
EOMONTH(CurrentMonth, -1),
-12,
MONTH
),
[Rate]
),
BLANK()  \\ or you can put some default value if both CurrentRate and LastRate are blank
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Re: Self-reference with DAX for Average

Ldomal — Tue, 26 Nov 2024 16:35:16 GMT

Hi Kedar_Pande thanks a lot for your quick response, it did not worked since the [Rate] is being fetched from a measure not a physical table, so it can't be referenced in the variable LastDate.

If it helps more on the detail of the current DAX I tried to used this is being evaluated in DAX Query so the first screenshot of table provided shows the output in DAX Query and not an actual table.

Re: Self-reference with DAX for Average

Anonymous — Thu, 28 Nov 2024 01:34:44 GMT

Hi Ldomal ,

Could you please provide sample data or pbix file(does not contain sensitive data)? That will help us reproduce the problem and provide solution.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best regards,

Mengmeng Li

Re: Self-reference with DAX for Average

Ldomal — Thu, 28 Nov 2024 16:27:10 GMT

Hi everyone it appears we have manage to solve the problem, for the purpose of everyone have the solution to this problem I'm attaching the PBIx file where we are showcaseing the solutution to the problem.

Due to internal company restriction we can't share a Cloud Drive Link but here I provide all the data and sample data as well as the DAX fomulas used:

Date	Total	Input	Input Rate	Output	Output Rate	Net
01/09/2023	10060	273	0.027137	-241	-0.02396	32
01/10/2023	10208	261	0.025568	-113	-0.01107	148
01/11/2023	10474	289	0.027592	-23	-0.0022	266
01/12/2023	10375	103	0.009928	-202	-0.01947	-99
01/01/2024	10159	74	0.007284	-290	-0.02855	-216
01/02/2024	10351	250	0.024152	-58	-0.0056	192
01/03/2024	10428	137	0.013138	-60	-0.00575	77
01/04/2024	10373	166	0.016003	-221	-0.02131	-55
01/05/2024	10466	162	0.015479	-69	-0.00659	93
01/06/2024	10541	267	0.02533	-192	-0.01821	75
01/07/2024	10652	264	0.024784	-153	-0.01436	111
01/08/2024	10599	177	0.0167	-230	-0.0217	-53
01/09/2024	10597	197	0.01859	-199	-0.01878	-2
01/10/2024	10386	96	0.009243	-307	-0.02956	-211
01/11/2024
01/12/2024
01/01/2025
01/02/2025
01/03/2025
01/04/2025

_Input = SUM('Table'[Input])

_Input Rate = DIVIDE([_Input], [_Total], 0)

_Input Rate Avg =

VAR _Period = DATESINPERIOD('Table'[Date], MAX('Table'[Date]), -13, MONTH)

RETURN

IF(

CALCULATE(COUNTBLANK('Table'[Input Rate]), ALLSELECTED('Table'[Date])) < 2,

[_Input Rate],

IF(

ISBLANK([_Input Rate]),

CALCULATE(AVERAGEX('Table', [_Input Rate]), _Period),

[_Input Rate]

)

_Input Rate Budget =

VAR _Period = DATESINPERIOD('Table'[Date], MAX('Table'[Date]), -13, MONTH)

RETURN

IF(

ISBLANK([_Input Rate]),

CALCULATE(AVERAGEX(_Period, [_Input Rate Avg])),

[_Input Rate]

)

_Net = SUM('Table'[Net])

_Output = SUM('Table'[Output])

_Total = SUM('Table'[Total])

Final Output