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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.