Calculate split sum within this year and next year

[donaldo]

Hi,

I have a main table with projects with ID's. They are related one-to-many to another table like this

ID	Month	Sales
A	okt-18	10
A	nov-18	10
A	dec-18	10
A	jan-19	10
A	feb-19	10
A	mar-19	10

Now I want to make a column called "Savings this Year", that for Project A will sum all sales within YEAR(TODAY()) and another column for all savings contained within YEAR(TODAY())+1. I managed to do the first column via my Date Table by the following, with correct and working numbers:

Savings in this year = CALCULATE(SUM(SavingsDistribution[Saving ('000)]);'Date Dimensions'[Year]=YEAR(TODAY()))

If I try to create the Next Year column I get an error related to dependency:

A circular dependency was detected: Idea[Column], Idea[Savings in this year], Idea[Column].

How can I either avoid this or do it in another way?

[v-cherch-msft]

Hi @donaldo

You may create a measure as below. Attached the sample file for your reference.If it is not your case,please share your sample data which could reproduce your scenario so that we could help further on it.

Savings Next Year =
CALCULATE (
 SUM ( SavingsDistribution[Sales] ),
 'Date Dimensions'[Year]
 = YEAR ( TODAY () ) + 1
)

Regards,

Cherie

[donaldo]

Hey v-cherch-msft,

Thanks for your help!
It worked as a measure. It's the same DAX as my first column. Do you have any idea as to what is wrong so that it can't be columns?

[v-cherch-msft · Accepted solution]

Hi @donaldo

The error is because you used two calculate function in two columns. Here is the article about this reason. Here is the article about avoiding-circular-dependency-errors-in-dax. In this case, measure would be better then calculated column.

Regards,

Cherie