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

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

Hi weesgaard,

Could you please share your formula to create column "TG 02.17" for further analysis?

Best Regards,
Angelia

weesgaard's avatar
weesgaard
Frequent Visitor
9 years ago

I believe the formular for TG2 should look like this:

TG 02.17 = IF(AND(Lejer[UdDato]>DATE(2017;01;31);Lejer[IndDato]<DATE(2017;03;1));CALCULATE(SUM(Lejer[UdDato])-CALCULATE(SUM(Lejer[IndDato])))+IF(Lejer[IndDato]<DATE(2017;02;01);CALCULATE(SUM(Lejer[IndDato])-DATE(2017;02;01)))-IF(Lejer[UdDato]>DATE(2017;02;28);CALCULATE(SUM(Lejer[UdDato])-DATE(2017;03;1))))

 

 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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