How to use CALCULATE with PREVIOUSYEAR?

[Anonymous]

I have the following formula:

Measure = CALCULATE(COUNT(table[column]); PREVIOUSYEAR(table[dates]))

which dies due to duplicate values in table[dates], which is apparently not supported by PREVIOUSYEAR (is there a particular reason why it cannot handle duplicates?), so I have modified the expression as follows:

Measure = CALCULATE(COUNT(table[column]); PREVIOUSYEAR(DISTINCT(table[dates])))

which again dies due to duplicate values. How is it possible that DISTINCT returns duplicates? I based the expressions on the official quick start basics and the function definitions: 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-quickstart-learn-dax-basics/

https://msdn.microsoft.com/en-us/library/ee634770.aspx

https://msdn.microsoft.com/en-us/library/ee634943.aspx

Thank you for your help guys!

[vanessafvg]

@Anonymous you need to use a date table, have you got one?

[Anonymous]

I have only one table with the data and a column with dates. Do I need a separate table?

[vanessafvg · Accepted solution]

@Anonymous yes you do its best practice when using date functions

its easy to create one to either use the calendarauto() function in dax, or i usually go for this

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

link your date to your date (that will remove the duplication, as date functions expect continous data)