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

 

Thanks,

PS1018

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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)))
  • 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

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        6 years ago

        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.

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi Anonymous 


    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.