AuroraBorealis13
New Member
- Joined
- Apr 11, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi, I inherited a power pivot worksheet which all of a sudden started misbehaving. it has several measure custom formulas like the one below.
There is a formula corresponding to each col, differing in the highlighted portion.
all of a sudden, refreshing started giving the above error, complaining about the 2Y.
I checked the valuedate_list table, which is laid out like so:
and all the dates are in proper date format.
I did find threads re this error but only as it applies to BI, so any suggestions/ ideas really appreciated!
thanks
There is a formula corresponding to each col, differing in the highlighted portion.
all of a sudden, refreshing started giving the above error, complaining about the 2Y.
I checked the valuedate_list table, which is laid out like so:
ValueDate | col_1M | col_YTD | col_3M | col_6M | col_12M | col_2Y |
Jan-18 | 1/31/2018 0:00 | 1/31/2018 0:00 | 11/30/2017 0:00 | 8/31/2017 0:00 | 2/28/2017 0:00 | 2/29/2016 0:00 |
Feb-18 | 2/28/2018 0:00 | 1/31/2018 0:00 | 12/31/2017 0:00 | 9/30/2017 0:00 | 3/31/2017 0:00 | 3/31/2016 0:00 |
I did find threads re this error but only as it applies to BI, so any suggestions/ ideas really appreciated!
thanks
2Y:=VAR Calculation_EndDate = MAX(ValueDate_List[ValueDate]) |
VAR Calculation_StartDate = MIN(ValueDate_List[col_2Y]) |
VAR Dates = DATESBETWEEN(Facts[ValueDate],Calculation_StartDate,Calculation_EndDate) |
VAR ProductFn = CALCULATE(PRODUCTX(Dates,1+ Round([Return Internal],4))-1,Dates) |
RETURN |
ProductFn |