Geting Count of Days

[kevingeorge3]

How would I calculate the number of days in each month between two dates in powerBI?
Example: Given start date of 12/09/2010 and end date 07/11/2010 I would like to get back

09/2010 - 22 days
10/2010 - 31 days
11/2010 - 7 days

The start and end dates vary and could possibly be in the same month or across two months or even more.

[v-diye-msft · Accepted solution]

Hi @kevingeorge3 

You can use the below measures:

201009 = IF(MIN('Table'[StartDate])>DATE(2010,9,30)||MAX('Table'[EndDate])<DATE(2010,9,1),0,IF(MIN('Table'[StartDate])<=DATE(2010,9,1)&&MAX('Table'[EndDate])>=DATE(2010,9,30),30,IF(MIN('Table'[StartDate])>DATE(2010,9,1)&&MAX('Table'[EndDate])>=DATE(2010,9,30),DATEDIFF(MIN('Table'[StartDate]),DATE(2010,9,30),DAY)+1,IF(MIN('Table'[StartDate])<=DATE(2010,9,1)&&MAX('Table'[EndDate])<DATE(2010,9,30),DATEDIFF(DATE(2010,9,1),MAX('Table'[EndDate]),DAY)+1,DATEDIFF(MIN('Table'[StartDate]),MAX('Table'[EndDate]),DAY)+1))))

201010 = IF(MIN('Table'[StartDate])>DATE(2010,10,31)||MAX('Table'[EndDate])<DATE(2010,10,1),0,IF(MIN('Table'[StartDate])<=DATE(2010,10,1)&&MAX('Table'[EndDate])>=DATE(2010,10,30),31,IF(MIN('Table'[StartDate])>DATE(2010,10,1)&&MAX('Table'[EndDate])>=DATE(2010,10,31),DATEDIFF(MIN('Table'[StartDate]),DATE(2010,10,31),DAY)+1,IF(MIN('Table'[StartDate])<=DATE(2010,10,1)&&MAX('Table'[EndDate])<DATE(2010,10,31),DATEDIFF(DATE(2010,10,1),MAX('Table'[EndDate]),DAY)+1,DATEDIFF(MIN('Table'[StartDate]),MAX('Table'[EndDate]),DAY)+1))))

201011 = IF(MIN('Table'[StartDate])>DATE(2010,11,30)||MAX('Table'[EndDate])<DATE(2010,11,1),0,IF(MIN('Table'[StartDate])<=DATE(2010,11,1)&&MAX('Table'[EndDate])>=DATE(2010,11,30),30,IF(MIN('Table'[StartDate])>DATE(2010,11,1)&&MAX('Table'[EndDate])>=DATE(2010,11,30),DATEDIFF(MIN('Table'[StartDate]),DATE(2010,11,30),DAY)+1,IF(MIN('Table'[StartDate])<=DATE(2010,11,1)&&MAX('Table'[EndDate])<DATE(2010,11,30),DATEDIFF(DATE(2010,11,1),MAX('Table'[EndDate]),DAY)+1,DATEDIFF(MIN('Table'[StartDate]),MAX('Table'[EndDate]),DAY)+1))))

You can also use M code to do this, but it would be a little bit complicated, please refer to the similar solved post:

https://community.powerbi.com/t5/Desktop/Days-in-Quarter-custom-colomn/td-p/736595 

[amitchandak]

from where these dates are coming from. Are they part of table or they are selected in Sclicer.

[kevingeorge3]

Hello,

they are coming from a table.

[amitchandak]

Try This a new column not measure

datediff = datediff(d1,d2,DAY)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[kevingeorge3]

That works to get the total days in the period, 

so that data reprsents days worked  so if I wanted a chart that shows the number of days worked by each year how would I do that if the start and stop dates have different years?