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.

     

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
Icon for Community Support rankCommunity 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
      Icon for Community Support rankCommunity 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.

       

      • SunilRaj's avatar
        SunilRaj
        Frequent Visitor
        4 years ago

        Thanks