cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 for this issue?

 

Thanks,

PS1018PowerBI.PNG

8 REPLIES 8
Highlighted
Super User IX
Super User IX

Re: Column that returns prior year value

This one should be created as measure.

I use following

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year))))
last year measure =CALCULATE(SUM(Sales[Sales Amount]),(dateadd('Date'[Date],-1,Year)))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Column that returns prior year value

Hi @PS1018 ,

 

you have to create a Measure.

 

Look at this.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 

Remember, you compute the ratio of the sums, not the sum of the ratio.

 

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Community Support
Community Support

Re: Column that returns prior year value

Hi @PS1018 ,

 

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.

Highlighted
Frequent Visitor

Re: Column that returns prior year value

Hi @v-xicai ,

 

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

 

Thanks,

PS1018PowerBI.PNG

Highlighted
Super User IV
Super User IV

Re: Column that returns prior year value

Hi @PS1018 

 

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Frequent Visitor

Re: Column that returns prior year value

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

Highlighted
Super User IV
Super User IV

Re: Column that returns prior year value

Hi,

that's the use case for SUMX.
Look at this.
https://docs.microsoft.com/en-us/dax/sumx-function-dax

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Super User IV
Super User IV

Re: Column that returns prior year value

Hi @PS1018 


is your problem solved?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors