Difference between measure and calculated column

[jsteffe]

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.

[v-kelly-msft · Accepted solution]

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]

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]

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]

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!

[jsteffe]

Sorry I did'nt see your reply.

It perfectly works. Thanks.