Hello excellent Power BI community.
For so long you have helped me via the resolution of other users' problems. But now... I have reached a point where I just can't find the answer to this problem. Hence my first post here.
My aim is to return the previous value in the "Measure" column based on criteria from other columns. My sample data for how it is supposed to be is below:
| Year | Month | Var 1 | Var 2 | Measure | Calc1 (Measure) | Calc2 (Measure) |
| 2022 | February | 0 | 2 | 0.00% | 3.33 | 0 |
| 2022 | March | 0 | 2 | 0.00% | 4 | 0 |
| 2022 | April | 0 | 4 | 0.00% | 2.67 | 0 |
| 2022 | May | 0 | 44 | 0.00% | 16.67 | 0 |
| 2022 | June | 9 | 186 | 2.88% | 78 | 2.25 |
| 2022 | July | 0 | 264 | 1.37% | 164.67 | 2.25 |
| 2022 | August | 0 | 61 | 1.32% | 170.33 | 2.25 |
| 2022 | September | 0 | 21 | 0.00% | 115.33 | 0 |
| 2022 | October | 0 | 43 | 0.00% | 41.67 | 0 |
| 2022 | November | 0 | 4 | 0.00% | 22.67 | 0 |
| 2022 | December | 1 | 0 | 1.60% | 15.67 | 0.25 |
| 2023 | January | 0 | 0 | 1.60% | 1.33 | 0.25 |
| 2023 | February | 0 | 0 | 1.60% | 0 | 0.25 |
The logic I have attempted so far is:
Measure = IF (AND ([Var 1] = 0 , [Var 2] = 0) = TRUE, CALCULATE ('Table1'[Measure], DATEADD('Date'[Date], -1, MONTH)), IF ( DIVIDE ([Calc2] , [Calc1] , 0) > 1, 1, DIVIDE ([Calc2] , [Calc1] , 0)))
But, as you clever people will already know, I am getting a circular reference error due to calling the [Measure] field within the [Measure] measure.
Please can you help me understand how I should I go about resolving this?
