Forum Discussion

MarionKeller's avatar
MarionKeller
New Member
7 years ago

DateDiff with Direct Query as Measure

I need to have the Date Difference between two Date columns   # of Minutes = Datediff(OP_ORDER_ITEM_ARCHIVE[PICKING_AT];OP_ORDER_ITEM_ARCHIVE[REFILLING_AT];MINUTE)   I'm using the DateDiff Func...
luke611's avatar
luke611
Frequent Visitor
6 years ago

lbendlin many thanks for swift reply. Unfortunately there is another error raised when I change DATEDIFF into simple minus between two dates: 

ORA-00904: "t0"."DATE_COLUMN": invalid identifier. The exception was raised by the IDbCommand interface.

lbendlin's avatar
lbendlin
Super User
6 years ago

As you can see this is an error on the Oracle side. Verify that you got the field name right.

  • luke611's avatar
    luke611
    Frequent Visitor
    6 years ago

    Thank you Guys for your help. It seems that there is an issue on PBI side when Oracle 11g is being used as a source. PBI has a problem in building proper SQL. I've found two kinds of workarrounds: swith to Import or use own query to get data. Unfortunately both bring a lot of limitation.

    I also tried to create measures but actualy I don't know how to solve it using only the measures. I need to calculate avarage of date differences for all rows. So let's say that I have a table with payments, first I need to calculate payment lag and then aggregate lags as an avarage for some dimensions.