Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am at a bit of a loss here and need your help.
I've been trying to write a fairly complex function in M and keep getting stuck at a point here.
Main target is to use a bunch of parameters and lists to extract a service time from StartDate to EndDate.
fxWorkingDays is a custom function that's working similar to "NetWorkdays" in Excel and delivers the raw number of days from StartDate to EndDate.
However, in order to get the service time, I need the value in [hh]:mm format, i.e. 48:30. Hence I need to fiddle around with the number a bit.
The "duration" type is what I am looking for, as it's providing the correct format, but I need to add and substract some things to the calculated time.
The error I keep getting is Expression.error: The name "ServiceTime" wasn't recognized. Make sure it's spelled correctly.
Let me paraphrase my code here - I will remove the unnecessary bits, as some of it is already working fine:
(StartDate as datetime, EndDate as datetime) as duration => let <variable declaration> ServiceTime = if ( <statement that returns "false" if the ServiceTime needs to be calculated> ) then ( ServiceTime = #duration(0,0,0,0) ) else ( let // Calculate raw number of days and multiply it with the duration of "1 day" C_ServiceTimeInDays = #duration(1,0,0,0) * fxWorkingDays(StartDate, EndDate, {holidays}), // Check if StartDate needs to be substracted. If yes, mark it as such C_StartDateIsWorkDay = if <statement that returns "true"> then #duration(1,0,0,0) else #duration(0,0,0,0), // Check if EndDate needs to be substracted. If yes, mark it as such C_EndDateIsWorkDay = if <statement that returns "true"> then #duration(1,0,0,0) else #duration(0,0,0,0), // At this point I know what I need to do (logically) // I need to return the "ServiceTime", which is the result of the outer "else" case:
// Therefore, I need to calculate the duration, which is the "ServiceTimeInDays". Logically, this is:
// "C_ServiceTimeInDays" minus "C_StartDateIsWorkDay" minus "C_EndDateIsWorkDay"
// However, this does not work and I get above error: ServiceTimeInDays = C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay in ServiceTimeInDays ) in ServiceTime
I believe my issue here lies in the "in" and returned statements, but I can't figure it out for the life of me...
I had also tried it with this, which brings another error message:
(StartDate as datetime, EndDate as datetime) as duration => let <variable declaration> ServiceTime = if ( <statement that returns "false" if the ServiceTime needs to be calculated> ) then ( ServiceTime = #duration(0,0,0,0) ) else (
<without the second "let ... in ...", but still has the same calculations> #duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay) ) in ServiceTime
// RESULT: "Expected Token: Identifier" at this point:
#duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay)
^
In this case, if I instead type
ServiceTime = #duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay)
//RESULT: "The variable with the name "ServiceTime" is already defined in this area"
I really hope, someone can help...
Solved! Go to Solution.
Hi @_n_MarianLein,
remove the part with "ServiceTime =" before empty duration.
(StartDate as datetime, EndDate as datetime) as duration => let <variable declaration> ServiceTime = if ( <statement that returns "false" if the ServiceTime needs to be calculated> ) then (ServiceTime =#duration(0,0,0,0)
Next point is
#duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay)
which is definitely not correct, just use C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay.
Btw. a similar task has been solved in Date / Time difference excluding weekends and factoring working ours, you can also inspire also there.
Hi @_n_MarianLein,
remove the part with "ServiceTime =" before empty duration.
(StartDate as datetime, EndDate as datetime) as duration => let <variable declaration> ServiceTime = if ( <statement that returns "false" if the ServiceTime needs to be calculated> ) then (ServiceTime =#duration(0,0,0,0)
Next point is
#duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay)
which is definitely not correct, just use C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay.
Btw. a similar task has been solved in Date / Time difference excluding weekends and factoring working ours, you can also inspire also there.
Thanks for pointing me to that thread!
I was able to adapt this to my needs, also leveraging some of what I did in my formula.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.