Forum Discussion

weesgaard's avatar
weesgaard
Frequent Visitor
9 years ago
Solved

Calculate days per month based on date interval

I need help finding the right formula to calculate number of days per month based upon my "IndDato" and "UdDato". If the "IndDato" shows 15-01-2017 and "UdDato" shows 30-06-2017, I need a column for...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi weesgaard,

    As I test, your TG 01,17 returns the error message as follows.



    Please create 01,17 using the formula, you will get expected result.

    01.17 = IF(Lejer[IndDato]<=DATE(2016,12,31),IF(Lejer[IndDato]<DATE(2017,02,1),DATEDIFF(DATE(2017,1,1),Lejer[UdDato],DAY),31),IF(Lejer[UdDato]<DATE(2017,02,1),DATEDIFF(Lejer[IndDato],Lejer[UdDato],DAY),DATEDIFF(Lejer[IndDato],DATE(2017,1,31),DAY)))



    Then you can get the 02,17 using the similar formula.

    Best Regards,
    Angelia

weesgaard's avatar
weesgaard
Frequent Visitor
9 years ago

When I insert your new formula, I get an error:

 

 

Any idea how to fix this?

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi weesgaard,

Please replace the DATE(2016,12,31) to DATE(2016;12;31), you should use the delimiter ";" rather than "," in your power BI desktop.

Best Regards,
Angelia

  • weesgaard's avatar
    weesgaard
    Frequent Visitor
    9 years ago

    Hi Angelia

     

    That solved the issue, but now I get a new error:

     

    The startdate can´t be after the end-date when using datediff. - any idea how to solve this?

     

    Thanks :)

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi weesgaard，

    Please debug the error based on the message. Please review the usage of DATEDIFF, startdate must be smaller than enddate, please check DATE(2017;1;1) and Lejer[Uddate], which is bigger.

    Best Regards,
    Angelia