Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Column that returns prior year value

Hi All,    I am trying to add a column in to my table model that returns Prior Year amounts. Please see attached. For some reason my current formula is not working. Does anyone know how to solve fo...
Need Help
v-xicai's avatar
v-xicai
Community Support
6 years ago

Hi Anonymous ,

 

Your formula is correct, while the SAMEPERIODLASTYEAR function should be used in measure instead of calculated column, see the link: https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax.

 

If you need to get the prior year amounts using a calculated column, you may create columns like DAX below.

 

Year= 'HFM Extract lc' [Date]
 
Prior year amounts= CALCULATE(SUM('HFM Extract lc' [Amount]), FILTER('HFM Extract lc', 'HFM Extract lc'[Year]=EARLIER('HFM Extract lc'[Year])-1))

 

Best Regards,

Amy

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-xicai ,

 

When I plug in that formula no values are returned. Am I doing this correctly?

 

Thanks,

PS1018