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,

Based on my understanding, if "IndDato" shows 15-01-2017 and "UdDato" shows 30-06-2017, you will add 16 days for Jan, 28 days for Feb, 31 days for Mar, 30 days for Apr, 31 days for May, and 31 days for Jue, right? If it is, you need to create mutiple calculated columns? And the columns are different for different "IndDato" and "UdDato". It's hard to achieve this, there is no regular rules here.

Best Regards,
Angelia

weesgaard's avatar
weesgaard
Frequent Visitor
9 years ago

Hi Angelia

Thanks for your reply.

You are absolutely correct about multiple calculated columns.

 

I might have found a formula that works for january:

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

 

However when I try to dublicate it for a new column "TG 02.17" and I change the date intervals in the formula - I get en error on circular references. Do you know why?

  • 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