Forum Discussion

evgeniam's avatar
evgeniam
Frequent Visitor
6 years ago
Solved

Error - The start date in Calendar function can not be later than the end date

  I get an error when trying to load a table visual which relies on a Work Days within Timeframe calculation. I have validated the measure results and it works as expected at the employee level, acc...
Dashboards
Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    6 years ago

    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 

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
6 years ago

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 

evgeniam's avatar
evgeniam
Frequent Visitor
6 years ago

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)