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
ServiceTimeI 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...
