Custom column ToF

[mitchpj75]

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!

[MFelix · Accepted solution]

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.

[v-janeyg-msft]

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

[mitchpj75]

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 · Accepted solution]

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.

[mitchpj75 · Accepted solution]

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.