cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mitchpj75
Regular Visitor

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



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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.