Forum Discussion
Error - The start date in Calendar function can not be later than the end date
Hi evgeniam,
From the error message, the issue should be related to the first parameter value in CALENDAR() function is larger than the second parameter value. As you use three IF() functions in result section and each IF() uses CALENDAR() function, I would suggest you return one IF() condition result each time to decide which IF() function has the issue.
In my opinion, the issue is possibly related to last two IF() parts, as we can't ensure the MAX ( start_Date, hire_Date ) is less or equal to the termination_Date in second IF() part. It's the same as MAX ( start_Date, hire_Date ) in last IF() part.
Best Regards,
Qiuyun Yu
Community SupportHi evgeniam,
From the error message, the issue should be related to the first parameter value in CALENDAR() function is larger than the second parameter value. As you use three IF() functions in result section and each IF() uses CALENDAR() function, I would suggest you return one IF() condition result each time to decide which IF() function has the issue.
In my opinion, the issue is possibly related to last two IF() parts, as we can't ensure the MAX ( start_Date, hire_Date ) is less or equal to the termination_Date in second IF() part. It's the same as MAX ( start_Date, hire_Date ) in last IF() part.
Best Regards,
Qiuyun Yu
Thank you for looking into this! I will investigate further.
I was wondering if this could be a bug considering when I create a table visual with Employee, Hire Date, Termination Date and Work Days within Timeframe columns, it works and I get the correct number of days listed for each employee (depended on the date slicer selection). Why does the measure work in that context, but not when used in a calculation within another measure?
Got it! It was in the second IF (). Fixed by changing
IF(AND(termination_Date<end_Date, termination_Date>start_Date)
to
IF(termination_Date<end_Date && termination_Date>start_Date &&termination_Date>hire_Date)
