Forum Discussion

Coelijoeli's avatar
Coelijoeli
Advocate I
10 years ago

Days between two dates columns

Hello community,   I'm building a dashboard with Power BI Desktop. I've connected a SQL server database which has two date fields:   [Transactiondate] [Closed]   What I want to do is get the ...
Need Help
dramus's avatar
dramus
Continued Contributor
10 years ago

There's also the DATEDIFF DAX function.

 

Days between = DATEDIFF([Transactiondate],[Closed],DAY)

gkhare's avatar
gkhare
Frequent Visitor
10 years ago

Not working for me! I' am trying to take no.of days between close_date and start_date, it throws an error which says-The third argument Interval can only be one of the following: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND...

I' am using the following expression

DaysLenght = DATEDIFF(AA_SALES_FACT[CLOSE_DATE],(AA_SALES_FACT[START_DATE]),DAY())

Request all gurus to kindly help me in fixing the above. I need to take the difference between Close_Date and Create_Date

 

 

  • HarrisMalik's avatar
    HarrisMalik
    Continued Contributor
    10 years ago

    gkhare there is a mistake in your formula the third argument to DATEDIFF is Interval not function. Use following formula:

     

    DaysLenght = DATEDIFF(AA_SALES_FACT[CLOSE_DATE],AA_SALES_FACT[START_DATE],DAY)

  • HarrisMalik's avatar
    HarrisMalik
    Continued Contributor
    10 years ago

    gkhare there is a mistake in your formula the third argument to DATEDIFF is Interval not function. Use following formula:

     

    DaysLenght = DATEDIFF(AA_SALES_FACT[CLOSE_DATE],AA_SALES_FACT[START_DATE],DAY)

     

    Edit: I do not know your data model just to be sure the first argument of DATEDIFF() is Start Date, second is End date and third is interval i.e. DAY, MONTH,YEAR etc

     

    DATEDIFF(<start_date>, <end_date>, <interval>)

     

    • WendyMc's avatar
      WendyMc
      New Member
      10 years ago

      Thanks for posting - just what I was looking for

      • peymanrmt's avatar
        peymanrmt
        New Member
        8 years ago

        Hi all,

         

        How do we calculate the time interval in hours between two dates while excluding the weekends and US holidays in the calendar? Any help is much appreciated.

    • MAPA's avatar
      MAPA
      Regular Visitor
      9 years ago

      Hello,

      I've used your suggestion which seem to be good but something is wrong there.

      Have you ever seen like this:

      Start dateEnd dateNo of days
      2016.12.092017.09.04269
      2016.12.092017.09.01532
      2016.12.092017.08.311855
      2016.12.092017.08.10244
      2016.12.092017.07.311170
      2016.12.092017.07.13216
      2016.12.092017.06.301015

       

      When the end date is the last day of the month it gives wrong result. :(

       

      Do you have any idea?

      • amarines's avatar
        amarines
        Frequent Visitor
        9 years ago

        Hello, what is the expresion you used??