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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
snehar
Frequent Visitor

how to calculate no of days in each month between startdate and enddate

Hi everybody I am newbie with DAX.

I have a vacation table, with startdate, enddate and employee id. I need to calculate no of days in each month(if start date and endate are two different months)
for example
id  start_date   end_date
10 17/05/2023 12/06/2023
I need to know howmany days in may and june separately
Any help would be much appreciated. Thanks all!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Please find attached my PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
snehar
Frequent Visitor

hey , is it possible to get no of days in each month between startdate and enddate, as a new column in the same table?

snehar_0-1690239829204.png

 

 

No, it is not.  If the start and end dates span multiple months then one rows for each day should be created.  You cannot create a row in a Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

if there is multiple months like the screenshot i shared, of diiferent employees (startdate and enddate of vactaion taken of each employee)how do i get no of days in each month 

The only way i am aware of is to transform the data in the Query Editor (I have already shared that file with you)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
snehar
Frequent Visitor

snehar_0-1690271222048.png

 

Ashish_Mathur
Super User
Super User

Hi,

Please find attached my PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Total_Days=DateDiff(Min(Start_Date) , Max(End_Date),Day)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.