Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DATEDIF months calculation

Hey guys, I'm having troubles with the DATEDIF function.

I'm trying to calculate the month difference between a project end date and the date of today.

 

I'm using:

 

Month Delay = DATEDIFF(ProjectCenter[Finish],TODAY(),MONTH)

And, for example, I get as result 3 in a project which ends 5/31/2018.

There are almost 25 days to that date, so the month delay is 2 and decimals.

 

If I use the Datedif function in EXCEL, I get the correct result.

 

Thank you for your help.

 

Regards

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi Anonymous,

    Please use the following formula and check if it works fine.

    MonthDelay=ROUND(DIVIDE(DATEDIFF(DATE(2018,5,31),TODAY(),DAY),30),0)


    Best Regards,
    Angelia

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous

     

    When you mention the interval as Month, Power BI extract the month number and calculate the difference. So, in your example it will be 8-5= 3 and it actually wont calculate the no of days.

     

    Thanks

    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      The only way to solve this is to calculate the day's dif and divide it to 30? 

       

      I think this is not a good way to calculate it since there are months with 31 days and others with 29...

  • chethan's avatar
    chethan
    Resolver III
    8 years ago

    Anonymous

     

    you Can Try this 

     

    Month Delay =
VAR Dateduration = 'Sales Data'[Ship Date] - 'Sales Data'[OrderDate]
RETURN
    DIVIDE ( Dateduration, 12, 0 )
  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

    Please use the following formula and check if it works fine.

    MonthDelay=ROUND(DIVIDE(DATEDIFF(DATE(2018,5,31),TODAY(),DAY),30),0)


    Best Regards,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I will try this function over the course of the days. But I must say that the result is the expected.

       

      Thank you!

  • rodrigosoaresss's avatar
    rodrigosoaresss
    Regular Visitor
    6 years ago
    That's how I've done it
    HELP =
    VAR I = InicialDate
    VAR F = FinalDate
    VAR __t =
    DIVIDE (
    EOMONTH ( I, 0 )
    - ( I - 1 ),
    EOMONTH ( I, 0 )
    - EOMONTH ( I, -1 )
    )
    + DATEDIFF (
    EOMONTH ( I, 0 ) + 1,
    EOMONTH ( F (), -1 + 1 ),
    MONTH
    )
    + DIVIDE (
    F ()
    - ( EOMONTH ( F (), -1 ) + 1 ),
    EOMONTH ( F (), 0 ) - EOMONTH ( F (), -1 )
    )
    return __t