Dynamic Date in select statement to Power BI Report Creation

[SunilRaj]

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_DESC
FROMOBJECTION OBJ,ASSESSMENT ASSESS ,TAX_TYPE TTY
WHERE OBJ.ENTERED_DATE <:P_DATE_FROM
AND OBJ.CLOSED_DATE ISNULL
AND ASSESS.ASSESS_NO = OBJ.ASSESS_NO
AND ASSESS.TAX_TYPE_NO = TTY.TAX_TYPE_NO
AND OBJ.TAX_CENTRE_NO=:P_TAX_CENTRE_NO
GROU PBY ASSESS.TAX_TYPE_NO , TTY.TAX_TYPE_DESC

[v-kkf-msft]

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]

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]

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.

[SunilRaj]

 I want to find dynamic average pending month as like this given below.

 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.

[v-kkf-msft]

Hi @SunilRaj ,

Does this meet your desired output?

M_DateDiff = 
CALCULATE (
    DATEDIFF ( MAX ( OBJECTION[ENTERED_DATE] ), MIN ( 'Calendar'[Date] ), DAY ) / 30,
    FILTER (
        OBJECTION,
        OBJECTION[ENTERED_DATE] < MIN ( 'Calendar'[Date] )
            && (
                OBJECTION[CLOSED_DATE] > MIN ( 'Calendar'[Date] )
                    || OBJECTION[CLOSED_DATE] = BLANK ()
            )
    )
)

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.

[SunilRaj]

Hello, your provided solution work for two month i.e july  and Aug then after  average value mismatch.

M_DateDiff = 
CALCULATE (
    DATEDIFF ( MAX ( OBJECTION[ENTERED_DATE] ), MIN ( 'Calendar'[Date] ), DAY ) / 30,
    FILTER (
        OBJECTION,
        OBJECTION[ENTERED_DATE] < MIN ( 'Calendar'[Date] )
            && (
                OBJECTION[CLOSED_DATE] > MIN ( 'Calendar'[Date] )
                    || OBJECTION[CLOSED_DATE] = BLANK ()
            )
    )
)

[v-kkf-msft · Accepted solution]

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,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SunilRaj]

Thanks

[v-kkf-msft]

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.