calculating days between 2 dates on direct query

[KatyJ44]

Hello everyone, we've recelently been able to connect Maximo into Power Bi through Azure databricks. 

i'm using this data to track maintenance legal compliance and i want to look at the days between 2 dates, Target Complete Date and Today, on my other Power BIs which are just ran from Excel sheets and sharepoints i can use all DAX functions and do this easily, but on this one i can not, i can see through research that DAX does not work well with Direct Queries, but i really want this to display on my dashboard. 

This is what i want to calculate,

Days until PM Due  = NOW() - '`hive_metastore` `maxmp76_raw` `workorder`'[TARGCOMPDATE] 

if anyone can help i would be greatful, Thank you!

[v-jialongy-msft]

Hi @KatyJ44 

You can try the following formula in Direct query model

Date diff = 
DATEDIFF(
    'Table'[Date],
    TODAY(),
    DAY
)

I connected a table using the direct query mode and tested it to be able to implement your needs.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@KatyJ44 Try:

Days until PM Due  = ( NOW() - '`hive_metastore` `maxmp76_raw` `workorder`'[TARGCOMPDATE] ) * 1.

[KatyJ44]

Hi Greg, unfortunalety it didnt work 😞

just out of curiosity what would the *1 at the end of the calculation do?

i'm self taught on PBI so i may have something formatted wrong in the background and DAX and DirectQuery are relatively new to me!

[Greg_Deckler]

@KatyJ44 Let's back up, are you trying to do this in a measure? If so, then you probably need something like this:

Days until PM Due  = ( NOW() - MAX('`hive_metastore` `maxmp76_raw` `workorder`'[TARGCOMPDATE]) ) * 1.

In DAX, dates are just decimal numbers. The integer portion is the number of days since December 30th, 1899 while the decimal portion is fractions of a day 1/24/60/60. So you can just subtract two dates and multiply by 1 in order to ensure you get back a number instead of a date.