I have a fact table, and a date table (marked as a date table in my report). I'm trying to do a visual comparing this year's calls with prior year's calls (user selects a year from a slicer). I've tried the following DAX measures, neither of which works:
Calls Prior Year = CALCULATE(DISTINCTCOUNT(Cad_Call[CC_rin]), USERELATIONSHIP(Cad_Call[DateKey_occur], DateList[DateKey]), Cad_Call[service_call] = "Y", SAMEPERIODLASTYEAR(Cad_Call[DateKey_occur])) + 0
Calls Prior Year = CALCULATE(DISTINCTCOUNT(Cad_Call[CC_rin]), USERELATIONSHIP(Cad_Call[DateKey_occur], DateList[DateKey]), Cad_Call[service_call] = "Y", SAMEPERIODLASTYEAR(DateList[DateKey])) + 0
There is no error when I save the measure, but the visual throws this error: A column spedified in the call to function SAMEPERIODLASTYEAR is not of typye DATE. This is not supported.
When I look at the MS DAX page, it looks like I should be able to use a date key in the SAMEPERIODLASTYEAR function.
When I change the formula to this:
Calls Prior Year = CALCULATE(DISTINCTCOUNT(Cad_Call[CC_rin]), USERELATIONSHIP(Cad_Call[DateKey_occur], DateList[DateKey]), Cad_Call[service_call] = "Y", SAMEPERIODLASTYEAR(DateList[Date])) + 0
I get the visual up, but it lists ALL years data, not just the data for the year I selected from the slicer.
I'm not sure what I'm doing wrong?
Thanks for any help!
