Forum Discussion

Ldomal's avatar
Ldomal
Frequent Visitor
1 year ago
Solved

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.

 

 

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

 

average
DAX
forecast
Power BI
  • Ldomal's avatar
    Ldomal
    1 year ago

    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
     
     

     

     

     

4 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

     

    • Ldomal's avatar
      Ldomal
      Frequent Visitor
      1 year ago

      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.


    • Ldomal's avatar
      Ldomal
      Frequent Visitor
      1 year ago

      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