Forum Discussion

KatyJ44's avatar
KatyJ44
New Member
2 years ago

calculating days between 2 dates on direct query

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!

 

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    KatyJ44 Try:

    Days until PM Due  = ( NOW() - '`hive_metastore` `maxmp76_raw` `workorder`'[TARGCOMPDATE] ) * 1.
    • KatyJ44's avatar
      KatyJ44
      New Member
      2 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.