Forum Discussion
Issue with CALCULATE & FILTER after adding a column formatted as "Date" data type
Hi Jolcyna ,
Is your [NonLinear_3YEARS_GTER_ForecastAnalysis_AllSLs] grouped by column [Year] of Year_Impact_Anaysis, maybe because you added a column [Date], which caused the previous grouping to fail.
You can add a &&'Year_Impact_Anaysis' column [Year] to the Filter of [NonLinear_3YEARS_GTER_ForecastAnalysis_AllSLs].
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Liu - THANK YOU very much for taking a time to read through my long post;-) In fact I don't think this formula has it. Please take a look. It's a formula that "forecast" revenues (historical data up to 2022 stored in 'ImpactAnalysis) by multiplying by CAGR (first three years of forecast) and then one thirds of CAGR for the following years.
'ImpactAnalysis table has only [Year] column which is a key with 'Year_Impact_Analysis table.
NonLinear_3YEARS_GTER_ForecastAnalysis_AllSLs =
VAR Lastyear = [GTER_ForwardAnalysis_LastYear_CAGR]
VAR No_of_Years =
SELECTEDVALUE ( Year_Impact_Analysis[Year] ) - [GTER_ForwardAnalysis_LastYear_CAGR]
VAR First_3_Years = CALCULATE ( SUM ( ImpactAnalysis[Sales] ), Year_Impact_Analysis[Year] = Lastyear )
* POWER ( ( 1 + [GTER_CAGR]), 3 )
RETURN
IF (
AND (SELECTEDVALUE ( Year_Impact_Analysis[Year] ) > Lastyear,SELECTEDVALUE ( Year_Impact_Analysis[Year] ) <= Lastyear+3),
CALCULATE ( SUM ( ImpactAnalysis[Sales]), Year_Impact_Analysis[Year] = Lastyear )
* POWER ( ( 1 + [GTER_CAGR]), No_of_Years )
,
IF (
SELECTEDVALUE ( Year_Impact_Analysis[Year]) > Lastyear+3, First_3_Years
*POWER ( ( 1 + ([GTER_CAGR]/3) ), No_of_Years-3)
))
I have a gut feeling that the issue is somewhere in this formula below but I have spent already 15 hours testing and did not figure it out. The blow formulas is supposed to define the ast year for which I have the actual data (so 2022 in my case).
[GTER_ForwardAnalysis_LastYear_CAGR] =