Forum Discussion
Self-reference with DAX for Average
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)RETURNIF(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)RETURNIF(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
Super UserCreate 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.