I am trying to create a measure that calculates the PRODUCT of the last 12 months. In the table below, column 1M = the current months values. Column 1Yr is my measure that is supposed to calculate the product of the prior 12 months values. As you can see, the 1Yr calculation seems to only result in the same value as 1M.
Here's my sample data.
| Date | 1M | 1Yr |
| 9/30/2024 | 1.0154 | 1.0154 |
| 8/31/2024 | 1.0193 | 1.0193 |
| 7/31/2024 | 1.0266 | 1.0266 |
| 6/30/2024 | 1.0086 | 1.0086 |
| 5/31/2024 | 1.0233 | 1.0233 |
| 4/30/2024 | 0.9599 | 0.9599 |
| 3/31/2024 | 1.0155 | 1.0155 |
| 2/29/2024 | 0.9792 | 0.9792 |
| 1/31/2024 | 0.9948 | 0.9948 |
| 12/31/2023 | 1.0599 | 1.0599 |
| 11/30/2023 | 1.0864 | 1.0864 |
| 10/31/2023 | 0.967 | 0.967 |
| 9/30/2023 | 0.9533 | 0.9533 |
| 8/31/2023 | 0.9843 | 0.9843 |
| 7/31/2023 | 0.9969 | 0.9969 |
| 6/30/2023 | 1.0098 | 1.0098 |
| 5/31/2023 | 0.9755 | 0.9755 |
| 4/30/2023 | 1.0095 | 1.0095 |
| 3/31/2023 | 1.0359 | 1.0359 |
| 2/28/2023 | 0.9544 | 0.9544 |
| 1/31/2023 | 1.0634 | 1.0634 |
| 12/31/2022 | 0.9876 | 0.9876 |
| 11/30/2022 | 1.0887 | 1.0887 |
| 10/31/2022 | 0.97 | 0.97 |
And here's my (incorrect) measure:
Rolling 1Yr =
VAR EndDate = MAX('100 and 107'[Date])
VAR StDate = EDATE(EndDate, -11)
VAR Result =
CALCULATE(PRODUCT('100 and 107'[1M]),
DATESBETWEEN('100 and 107'[Date], StDate,EndDate)
)
RETURN Result
