Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

Start date not being included in working days formula

I have a formula to calculate the number of working days between a projects start and end date. However, the formula is not including the start date of the project. For example - a project starts on May 18 2020 and the formula returns 9 working days, instead of 10. Please see the formula below (I have a date table with working days flagged):

 

 

wk.png

13 REPLIES 13
v-xuding-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Anonymous ,

 

I find that you have created a thread about this problem and it still hasn't been resolved. Can you please share some sample data and a few images about your situation?  It is helpful for us to understand your requirement and solve it quickly.

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

Syndicated - Outbound

@Anonymous , Most of the time in all such calculation you end up adding +1

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
parry2k
Super User
Super User

Syndicated - Outbound

@Anonymous use DATESBETWEEN function

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

Where should i add this?

Syndicated - Outbound

@Anonymous 

 

measure = 
CALCULATE ( COUNTROWS ( Date ),
   DATESBETWEEN ( Date[Date], __startdate>, __endate> ),
   Date[Day Type] = "Working Day"
)

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

Using DATESBETWEEN still returns the same (i.e., project starting 4/18 returns 9 days)

 

I do have a month column, so the calculation is expected to return the working days over the course of the month

Syndicated - Outbound

@Anonymous we are surely missing some here, can you share pbix file, remove sensitive information before sharing.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

@Anonymous I just looked at your date table and without the measure, you can see there are 9 working days between May 18th, 2020 and May 31st, 2020 and your measure is working fine, why you expected 10 days? What I'm missing here?

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

avatar user
Anonymous
Not applicable

Syndicated - Outbound

Example attached - focus area is May '20

 

 

avatar user
Anonymous
Not applicable

Syndicated - Outbound

ill work on it - thanks

Syndicated - Outbound

When you are returning

 

_result+1

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
avatar user
Anonymous
Not applicable

Syndicated - Outbound

I have a month column in a matrix visual - adding +1 adds an additional day to ever month - not just the starting month

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)