Forum Discussion

SunilRaj's avatar
SunilRaj
Frequent Visitor
4 years ago
Solved

Dynamic Date in select statement to Power BI Report Creation

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_...
Show and Tell
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

     

v-kkf-msft's avatar
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.

 

SunilRaj's avatar
SunilRaj
Frequent Visitor
4 years ago

 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's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
      SunilRaj
      Frequent Visitor
      4 years ago

      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's avatar
        v-kkf-msft
        Community Support
        4 years ago

        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.