Forum Discussion
Column that returns prior year value
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.
Hi v-xicai ,
When I plug in that formula no values are returned. Am I doing this correctly?
Thanks,
PS1018
Hi Anonymous
Why don't you want to use a measure?
If the row context is transformed into a filter context by calculate, is filtering over each column?
Does the line from the previous year only differ in the date? or also e.g. in FX rate etc.Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.I am trying to calculate organic growth for a region and am not able to do this for a measure because it adds the local currencies of the countries together before multiplying by the FX rates to convert back to USD. Because of this I need to translate all the accounts for each country back to USD using the prior year FX rates and then sum the results, so that currencies aren't mixed.
i.e. - I need (Country 1 $'s* PY FX rate) + (Country 2 $'s *PY FX rate) = Organic Dollars
The measure calculates the following (Country 1 $'s +Country 2 $'s) *(Country 1 PY FX rate+Country 2 PY FX rate) = Incorrect
