Card display with Don't Summarize option workaround

[nleuck]

Hello Everyone,

I'm trying to display a Years of Service card in Power BI. I have a column that has Employee Hire Date. I've created a column in Power BI called Today's Date using NOW( ). The problem I'm running into is when I put Years of Service on a card Power BI doesn't have a Don't Summarize option for that field. My only options are: Sum, Average, Minimum, Maximum, Count(Distinct), Count, Standard deviation, Variance, Median. I'm using Direct Query so my options are limited on what formulas I can use. Does anyone know of a workaround?

Thanks,

[jdbuchanan71 · Accepted solution]

@nleuck 

Would you be able to add a measure to the model to calculate the value?

Years of Service = 
VAR _HireDate = SELECTEDVALUE(Employees[Employee Hire Date])
RETURN DATEDIFF(_HireDate,TODAY(),YEAR)

[nleuck]

@jdbuchanan71 Thank you! That seemed to work. I double checked a few different examples and they all worked perfectly.