Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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.
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
97 | |
97 | |
73 | |
72 |