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 ,

 

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.

     

    • SunilRaj's avatar
      SunilRaj
      Frequent Visitor
      4 years ago

      Thanks