Forum Discussion

Kasiop's avatar
Kasiop
Helper II
6 years ago

Date issue

Hi,
 
Here is my issue:
I am trying to get the difference between two dates (I am using a direct query view), but on PBI desktop, I don’t see my fields as date format:

If anyone has any idea on why I cannot use the dateDiff or why I cannot see the date format for my two fields I will be more than happy.

 

thanks,

Naïma

 

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Kasiop - What happens if you do this:

     

    Measure = (MAX([Closure Date]) - MAX([Creation Date])) * 1.

     

    ?

    • Kasiop's avatar
      Kasiop
      Helper II
      6 years ago

      Thanks, it returns -40 does it mean I use this formula?

      • Kasiop's avatar
        Kasiop
        Helper II
        6 years ago

        Hi,

        after applying the formul, i am not sure the outcome is right... if you have another idea... that will be great.

        Thanks

        Kasiop_0-1598344713391.png

  • v-xicai's avatar
    v-xicai
    Community Support
    5 years ago

    Hi Kasiop ,

     

    You may select on the column [Closure Date], go to Column tools->Data type, change the data type to Date, do the same with column [Creation Date]. Then you may create measure instead of calculated column like DAX below.

     

    Measure= DATEDIFF(MAX(Table1[Creation Date]),MAX(Table1[Closure Date]),DAY)

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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

    • Kasiop's avatar
      Kasiop
      Helper II
      5 years ago

      Thanks Amy, but it is "kind of" a format date already but I still cannot use the Dax measure: 

       

       

       

      • v-xicai's avatar
        v-xicai
        Community Support
        5 years ago

        Hi Kasiop ,

         

        Based on my test which connect to SQL Server database using DirectQuery Mode in the latest version of Power BI Desktop, the DATEDIFF function works well.

         

         

        DateDiff = DATEDIFF(MAX(Sales[CreationDate]),MAX(Sales[ClosureDate]),DAY)

         

         

         

        >>Thanks Amy, but it is "kind of" a format date already but I still cannot use the Dax measure: 

         

        Is there any error message to appear when you create this measure?

         

        Best Regards,

        Amy 

         

        Community Support Team _ Amy

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