Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
Instead Power BI is doing this
Solved! Go to 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:
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 |
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:
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 |
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
24 | |
24 | |
22 |