Hello ,Please help as we are trying to create Power BI Report using oracl query.But the bold part is creating issue SELECT COUNT(*),ABS(AVG(TO_DATE(ENTERED_DATE)-TO_DATE(:P_DATE_FROM)))/30 AVG_AGE_OF_ACCOUNT,ASSESS.TAX_TYPE_NO TAX_TYPE_NO, TTY.TAX_TYPE_DESCFROMOBJECTION OBJ,ASSESSMENT ASSESS ,TAX_TYPE TTYWHERE OBJ.ENTERED_DATE <:P_DATE_FROMAND OBJ.CLOSED_DATE ISNULLAND ASSESS.ASSESS_NO = OBJ.ASSESS_NOAND ASSESS.TAX_TYPE_NO = TTY.TAX_TYPE_NOAND OBJ.TAX_CENTRE_NO=:P_TAX_CENTRE_NOGROU PBY ASSESS.TAX_TYPE_NO , TTY.TAX_TYPE_DESC

Hi SunilRaj , Do you want to calculate the average of all [ASSESS_NO]? Avg_diff = AVERAGEX ( VALUES ( OBJECTION[ASSESS_NO] ), [M_DateDiff] ) If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.Best Regards,WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dynamic Date in select statement to Power BI Report Creation

9 Replies

v-kkf-msft
Community Support
4 years ago
Hi SunilRaj ,

Have you tried importing data by using a native database query? Put your query in the SQL statement box, which appears when you expand the Advanced options section of the Oracle database dialog. Power BI Desktop does not support Oracle native queries that execute a stored procedure.

Since there is no Oracle database installed, the query I executed with Sql Server is as follows.

SELECT COUNT(*), ABS(AVG( DATEDIFF(dd,convert(DATE,[ENTERED_DATE]),convert(DATE,[:P_DATE_FROM]))))/30 AVG_AGE_OF_ACCOUNT FROM [CaseTest].[dbo].[Dates_2]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-kkf-msft
Community Support
4 years ago
Hi SunilRaj ,

Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz
- SunilRaj
  Frequent Visitor
  4 years ago
  I want to find dynamic average pending month in power bi report objection cases.
  e.g - one objection case enter in 11th June 2013 and still open.
  when I selected July 2013 then average pending month output will 0.67 and when I am select august 2013 then o/p will 1.7 and increasing as so on till date. and still case open so average pending month output for this month will 99.68.
  
  Thanks
  - v-kkf-msft
    Community Support
    4 years ago
    Hi SunilRaj ,
    
    I am not sure if I understood your question correctly. I created these example tables and data.
    
    ASSESSMENT:
    
    OBJECTION:
    
    TAX_TYPE:
    
    Two parameters:
    
    Then I add a custom column in OBJECTION table.
    
    = if [CLOSED_DATE] = null and [ENTERED_DATE] < Date.FromText(P_DATE_FROM) and [TAX_CENTRE_NO] = P_TAX_CENTRE_NO then Duration.Days([ENTERED_DATE] - Date.FromText(P_DATE_FROM)) else null
    
    Then create the relationship and measure.
    
    Measure = ABS ( AVERAGE ( OBJECTION[Diff] ) ) / 30
    
    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    
    Sample Data.pbix85 KB