User Profile
Ldomal
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Dataverse Connection
Hi everyone, We're currently experiencing an issue with Dataverse tables. When we try to establish a connection through Power BI Desktop to an environment on Power Platform to fetch a Dataverse table, we encounter the following error. Interestingly, we can successfully connect to the Dataverse tables using Dataflow Gen1. Does anyone have any suggestions on why the connection in Power BI Desktop isn't working? We've ensured that the TDS option is enabled in the admin center. Any insights would be greatly appreciated.Re: 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) 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 OutputSelf-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
