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
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 |
---|---|
159 | |
109 | |
96 | |
84 | |
75 |
User | Count |
---|---|
157 | |
137 | |
131 | |
81 | |
61 |