Forum Discussion
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!
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.
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.
4 Replies
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.
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.
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.
