A sample of my data looks like this: (sample spreadsheet attached):
The objective is to calculate the column Hard Cap Index using DAX. I can obviously do it in Excel, as per the spreadsheet.
The first column is the Competition Date, and the second, the Anchor Date, is one year prior.
The column E20 is a moving average of the best Eight of the last 20 scores. I can get this in DAX thanks to Avi in a previous post.
The Anchor is the lowest earlier value of the Hard Cap Index since the Anchor Date. For Round 1 there is no earlier round, so there is no Anchor. But for all succeeding rounds, there is value.
The column E20 - Anchor is as it says, and has a value for all rounds except the first.
Normally, the Soft Cap Index and the Hard Cap Index is the same as the E20.
The Soft Cap is different to the E20, ie the Index is capped, when the E20 - Anchor is greater than 3.0.
It is calculated as the Anchor + 3 + 0.5 * ([E20 - Anchor] - 3). So, in other words, once the E20 rises above the anchor by 3 or more, only half of this difference is allowed, in arriving at the Soft Cap Index. Perforce the Soft Cap Index can never exceed the E20.
If the E20 - Anchor is greater than 7.0, then there is a Hard Cap value of Anchor + 5.
Looking at another part of the sample, below, one can see on the highlighted row that
1. E20 - Anchor is 10.10
2. So the Soft Cap Index is 23.20 + 3 + 0.5 * (10.10 - 3) = 29.75.
Similarly for the Hard Cap Index, since E20 - Anchor is greater than 7, the Hard Cap Index is
23.2 + 5 = 28.20.
The circularity in DAX occurs because the Hard Cap Index is based on the Anchor, and the Anchor is based on the prior values of the Hard Cap Index.
It is not really circular, due to the word prior above.
In the Sample attached, I set up a table to be the data model:
I couldn't try to write a DAX measure for the Soft or Hard Cap Indices without one for the Anchor. So I set up a temporary measure Anchor1, based not on the minimum of the Hard Cap index, but temporarily on the E20:
I could then write Temporary SoftCapIndex1 and HardCapIndex1 in terms of Anchor1.
I then set up Anchor2 to refer to HardCapIndex1 instead of E20: (I was getting closer to what I wanted, namely an Anchor related to the correct Hard Cap Index, but it was not yet available.)
I could the write measures SoftCap2 and HardCap2 based on Anchor2.
The last piece of the puzzle would be to edit Anchor 2 so that it referred to HardCap2
And therein lies the problem.
The editor advises that there are no errors in the formula, but when I say OK, I get:
A circular dependency was detected: 'Table3'[Anchor2],'Table3'[HardCap2],'Table3'[Anchor2].
I agree that it is an apparent circular dependency, but was hoping that by using MINX, rather than MIN, the fact that it is not circular at the row by row level may provide a solution.
Any help would be greatly appreciated.
(I seem to have reached the limit on attached files? Three? I will try to attach the sample spreadsheet at a second post.)