Getting Monthly Percentages Without Interrupting Other Formulas

[Anonymous]

I have this data in two different tables:

What I'm aiming to do is to get the Positive % measure on my left table to match the positive Positive % measure values on my right table. I do not want the same number all the way down. The left table is using the date from my date table, the right table is using the survey date from my All Survey Data table, so those values would be correct.

My issue is, simply establshing a 1 to many relationship between these two tables causes my Retention column to become incorrect when I do so:

My retention numbers change when I need them to remain how they previously were. So THEN, I tried the workaround of making the relationship inactive and using USERELATIONSHIP but then this happens:

The formula it is saying it causing a circular dependency with is this formula:

So now from here, I do not know how to fix this. Any ideas?

[v-yulgu-msft]

Hi @Anonymous,

How did you calculate the Retention field?

Regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft So calculating the Retention consists of 3 different measures:

Active Clients:

Cients added within the last year:

Clients that were active 1 year ago from today:

The 3 pieces are then used in this formula which (I think) provides the trailing 12 month retention:

I then am able to take the [Month End] column from my date table to display these values: