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

[evgeniam]

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, accounting for start date, end date, hire and termination date, for all employees in the dataset, even if the termination date is hypothetical (in the future). But breaks when using it to calculate hours ([Work Days within Timeframe]*8)

Work Days wn Timeframe =

var start_Date=FIRSTDATE(ALLSELECTED('Calendar'[Date]))

var end_Date=LASTDATE(ALLSELECTED('Calendar'[Date]))

var hire_Date = MAX(Employee[HireDate])

var termination_Date = MAX(Employee[TerminationDate])

return

IF(AND(ISBLANK(termination_Date), hire_Date<=end_Date),

DATEDIFF(MAX(start_Date,hire_Date), end_Date, DAY)-COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(MAX(start_Date, hire_Date), end_Date), "DoW", WEEKDAY([Date], 1)), [DoW]=1||[DoW]=7)),

IF(AND(termination_Date<end_Date, termination_Date>start_Date),

DATEDIFF(MAX(start_Date, hire_Date), termination_Date, DAY)-COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(MAX(start_Date, hire_Date), termination_Date), "DoW", WEEKDAY([Date], 1)), [DoW]=1||[DoW]=7)),

IF(termination_Date>=end_Date,

DATEDIFF(MAX(start_Date, hire_Date), MIN(termination_Date, end_Date), DAY)-COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(MAX(start_Date, hire_Date), MIN(termination_Date, end_Date)), "DoW", WEEKDAY([Date], 1)), [DoW]=1||[DoW]=7)),

0)

))

[v-qiuyu-msft · Accepted solution]

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]

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)

[evgeniam]

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?