Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Ldomal
Frequent Visitor

Self-reference with DAX for Average

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.

 

Ldomal_1-1732589796972.png

 

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.

Ldomal_2-1732589975609.png

Instead Power BI is doing this

Ldomal_3-1732590027553.png

 

1 ACCEPTED SOLUTION

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:

DateTotalInputInput RateOutputOutput RateNet
01/09/2023100602730.027137-241-0.0239632
01/10/2023102082610.025568-113-0.01107148
01/11/2023104742890.027592-23-0.0022266
01/12/2023103751030.009928-202-0.01947-99
01/01/202410159740.007284-290-0.02855-216
01/02/2024103512500.024152-58-0.0056192
01/03/2024104281370.013138-60-0.0057577
01/04/2024103731660.016003-221-0.02131-55
01/05/2024104661620.015479-69-0.0065993
01/06/2024105412670.02533-192-0.0182175
01/07/2024106522640.024784-153-0.01436111
01/08/2024105991770.0167-230-0.0217-53
01/09/2024105971970.01859-199-0.01878-2
01/10/202410386960.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
 
Ldomal_0-1732811110814.png
 

 

 

 

View solution in original post

4 REPLIES 4
v-mengmli-msft
Community Support
Community Support

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

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:

DateTotalInputInput RateOutputOutput RateNet
01/09/2023100602730.027137-241-0.0239632
01/10/2023102082610.025568-113-0.01107148
01/11/2023104742890.027592-23-0.0022266
01/12/2023103751030.009928-202-0.01947-99
01/01/202410159740.007284-290-0.02855-216
01/02/2024103512500.024152-58-0.0056192
01/03/2024104281370.013138-60-0.0057577
01/04/2024103731660.016003-221-0.02131-55
01/05/2024104661620.015479-69-0.0065993
01/06/2024105412670.02533-192-0.0182175
01/07/2024106522640.024784-153-0.01436111
01/08/2024105991770.0167-230-0.0217-53
01/09/2024105971970.01859-199-0.01878-2
01/10/202410386960.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
 
Ldomal_0-1732811110814.png
 

 

 

 
Kedar_Pande
Community Champion
Community Champion

@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

 

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.


Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.