Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
weesgaard
Frequent Visitor

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 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:

2017-07-28_0920.png

1 ACCEPTED SOLUTION

Hi @weesgaard,

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

1.PNG

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)))

2.PNG

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

Best Regards,
Angelia

View solution in original post

9 REPLIES 9
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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

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?

Hi @weesgaard,

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

Best Regards,
Angelia

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))))

 

 

Hi @weesgaard,

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

1.PNG

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)))

2.PNG

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

Best Regards,
Angelia

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

 

2017-08-09_0958.png

 

Any idea how to fix this?

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

Hi Angelia

 

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

 

2017-08-10_1134.png

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

 

Thanks 🙂

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.