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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RobertReeves
Frequent Visitor

Need Difference between dates minus weekends and holidays

I am creating a report that needs to display the delta between 2 dates, minus weekends and holidays. I tried network days, but for some reason BI would not recognize NETWORKDAYS.

 

We used to accomplish this task in Excel by using =networkdays(f2,g2)-1 and then changing any 3s to a 2 for a month that has a holiday. 

 

I have a date table and a holiday table. The date table is linked to the main table by the date (G). I have created a column in the holiday table with just the month number and linked that with a month number in the main table.

Is there a way to use this to get to the final outcome needed below?

FGDelta F & GAfter FormulaFinal Outcome 
12/23/2022 19:2612/28/2022532
12/16/2022 19:3712/17/2022100
12/21/2022 17:1612/23/2022222
12/22/2022 21:0612/23/2022111
12/23/2022 12:1712/27/2022422
12/2/2022 20:4812/5/2022311
12/7/2022 19:2612/9/2022222
12/8/2022 19:2212/12/2022422
12/27/2022 18:4512/29/2022222
12/19/2022 21:1212/20/2022111
12/20/2022 11:4712/22/2022222
12/22/2022 19:2012/27/2022532
12/21/2022 21:2812/22/2022111
12/23/2022 12:2812/28/2022532
7 REPLIES 7
jthibault
New Member

Could you just use index columns in your calendar?

 

Add a weekday name column and filter out Sat and Sunday.

 

Import a holiday table and merge it to your calendar. (you can link to an online or a company one) Then just filter by entry = "null"

 

Add 2 index columns one starting at 0 the next starting at 1.

 

Merge the query to itself with the first column being index starting at 0 then starting at 1 and add the date column and label it previous date.

 

Then you will end up with both dates in the same row and can add whatever calculated column you would like to use on the two dates.

 

 

 

 

Padycosmos
Solution Sage
Solution Sage

Thank you!

Sahir_Maharaj
Super User
Super User

The above formula first checks if the "Delta F & G" column is blank. If it is, it returns a blank. If not, it checks if the month of the date in column G is the same as the related month number in the holiday table. If it is, it subtracts 2 from the "Delta F & G" value. If not, it subtracts 1.

 

This will give you the "Final Outcome" column as in the example you provided.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Sahir_Maharaj
Super User
Super User

Here is an example of a DAX formula that could be used for this calculation:

=IF(ISBLANK([Delta F & G]),BLANK(),IF(MONTH(G) = RELATED(Holiday[Month Number]), [Delta F & G] - 2, [Delta F & G] -1))

 

Assuming that the date table is linked to the main table by the date column (G), the holiday table is linked to the main table by the month number column, and the "Delta F & G" column is the number of days between the two dates.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Sahir_Maharaj
Super User
Super User

Yes, you can use DAX to calculate the final outcome needed. You can create a calculated column using DAX to determine the number of workdays between two dates, excluding weekends and holidays.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
SivaMani
Resident Rockstar
Resident Rockstar

@RobertReeves , You may need to maintain the holidays. I found the below post with steps to create a calendar table,

 

https://community.powerbi.com/t5/Community-Blog/Use-DAX-to-create-a-calendar-table-with-holidays/ba-...

Once you have the above table, you can take the count of working days by filtering the dates.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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