Forum Discussion

jsteffe's avatar
jsteffe
Icon for Helper III rankHelper III
6 years ago
Solved

Difference between measure and calculated column

Hello,

If I write this formula in a measure, I get the good result as it calculates the amount of sales for the next year.

CALCULATE(SUM(Sales[amount]);NEXTYEAR('Calendar'[Date]))
 
But if I put the same formula into a calculated column of table Sales, I get only blank values.
 
Can someone explain me why ?
 
Thanks for your help.
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi jsteffe ,

     

    It is because of the row context,you need to correct your expression as below:

     

    CALCULATE(SUM(Sales[amount]),'Sales',NEXTYEAR('Calendar'[Date]))

     

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

5 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi jsteffe ,

     

    It is because of the row context,you need to correct your expression as below:

     

    CALCULATE(SUM(Sales[amount]),'Sales',NEXTYEAR('Calendar'[Date]))

     

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Because you are in Row context, you likely need to break out of row context using ALL.

     

    CALCULATE(SUM(ALL(Sales[amount]));NEXTYEAR('Calendar'[Date]))

    • jsteffe's avatar
      jsteffe
      Icon for Helper III rankHelper III
      6 years ago

      Hi Greg,

      Your proposal :

      CALCULATE(SUM(ALL(Sales[amount]));NEXTYEAR('Calendar'[Date]))

      doesn't work because the first parameter of SUM function requires the name of a column.

       

      I created a table Calendar and a relationship between this table and my table Sales. That's why in the row context I thought that this formula will calculate for each row the total amount of sales for the next year (of my actual row)

      CALCULATE(SUM(Sales[amount]);NEXTYEAR('Calendar'[Date]))
      This one doesn't work too :
      CALCULATE(SUM(Sales[amount]);NEXTYEAR(Sales[SaleDate]))
       
       
       
      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi jsteffe ,

         

        Have you tried my measure?I have tested here and it works.

         

        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!