DAX Doesnt Support Comparison

Question

I have a table of Employees. The Table has both current and budgeted employees. Each employees has a salary, start date and if applicable a term date associated with them.

I am attempting for project the salary spend for each month for the next year based on the following rules:

Hire_Date <= Projected_Month

Term_Date > Projected_Month

Measure:

CALCULATE(sum(INPUT_TECH[Salary_USD]),filter(INPUT_TECH,INPUT_TECH[Hire_Date]<=MEASURE_SWB_SPEND[MEASURE_SWB_SPEND_DATE]&&INPUT_TECH[Term_Date]>MEASURE_SWB_SPEND[MEASURE_SWB_SPEND_DATE]))

The issue here is that if ther is no Term_date, the cell will be blank, and if the cell is blank, the term_date will always be less than the current projected month and therefore the answer will always be 0.

I attempted to fix this by changing the format of the termdate to always either have the number 100,000 formated to read "ACTIVE" or the date they were actually terminated Format: [=100000]"ACTIVE";[<>100000]mm/dd/yyyy

However, I still receive the following error:

DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

Answer 1

I would suggest not formatting the term date to the string "Active" and then using it for comparisons in your measures. This is why you may be getting that specific error. DAX is finding a data-type issue when making the comparisons. If you want the term to show up as "Active" where term dates are blank for visualization reasons, that may be okay. But I would establish a new calculated column that determines if a term date is blank and replaces it with a date type far into the future so that dates can be compared among dates—something like:

Term Date Blank Fix = 
IF(
    ISBLANK( INPUT_TECH[Term_Date] ),
    // The third argument below could be replaced with a hard year, ex. 2099
    DATE( 12, 31, YEAR( TODAY() ) + 10 ),
    INPUT_TECH[Term_Date]
)

Then you could use the above column to make your comparisons in your measures. Keep in mind that if you are using a date table with AUTOCALENDAR() that making a date with the year 2099 may wildly expand your date table.