Calculate days per month based on date interval

[weesgaard]

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 January showing 16 days, a column for February showing 28 days and so on. If the "UdDato" is blank I need the full number of days for the month to be shown.

My table looks like this:

[v-huizhn-msft]

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]

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]

Hi @weesgaard,

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

Best Regards,
Angelia

[weesgaard]

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 · Accepted solution]

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]

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

Any idea how to fix this?

[v-huizhn-msft]

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]

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]

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