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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mitchpj75
Helper I
Helper I

Custom column ToF

Hi,

 

Hopefully just quick advice and guidance.  I have 2 columns containing date information (the colmns have been set to datatype of Date in the Transform tab) - "Date of Referral" and "Date of initial attempted contact".

 

Using the below query I was able to calculate the number of days between the date columns but then I need to EXCLUDE weekends (saturday AND Sunday).  This will then give me the total number of days between the 2 dates, that I will then convert to hours (as I need to cases reponded to within 24hrs).

 

So my custom calc is:

 

[Date of initial attempted contact]-[Date of referral], NETWORKDAYS([Date of referral],[Date of initial attempted contact])

 

When I use my custom calculation of NETWORKDAYS I get a token EoF expected error.  What is wrong with my custom calc?

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

Hi @mitchpj75 ,

 

There is no NETWORKDAYS formula in M language or in DAX however the best M language expert in this community @ImkeF  create her own formula:

 

https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/

 

Just create a blank query and paste the code in that blank query now add a new column referencing that formula.

 

Check PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Thank you for the response! I did look at these as the solution but I still struggled however through further searching I was able to do what I need with the following:

 

Watched this first - https://www.youtube.com/watch?v=kRACuS4eKWA

Then used the code here to setup the UI and it worked - https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/

 

All seems overly complicated to work out the number of working days between dates but the solutions worked.

View solution in original post

4 REPLIES 4
v-janeyg-msft
Community Support
Community Support

Hi, @mitchpj75 

 

It's not very clear about your problem.

Could you mind providing some sample data and the desired result?So we can help you soon.

 

Best Regards

Janey Guo

 

All sorted - thanks for responding though.

 

Thank you for the response! I did look at these as the solution but I still struggled however through further searching I was able to do what I need with the following:

 

Watched this first - https://www.youtube.com/watch?v=kRACuS4eKWA

Then used the code here to setup the UI and it worked - https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/

 

All seems overly complicated to work out the number of working days between dates but the solutions worked.

MFelix
Super User
Super User

Hi @mitchpj75 ,

 

There is no NETWORKDAYS formula in M language or in DAX however the best M language expert in this community @ImkeF  create her own formula:

 

https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/

 

Just create a blank query and paste the code in that blank query now add a new column referencing that formula.

 

Check PBIX file attach.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you for the response! I did look at these as the solution but I still struggled however through further searching I was able to do what I need with the following:

 

Watched this first - https://www.youtube.com/watch?v=kRACuS4eKWA

Then used the code here to setup the UI and it worked - https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/

 

All seems overly complicated to work out the number of working days between dates but the solutions worked.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors