Issue with CALCULATE & FILTER after adding a column formatted as "Date" data type

[Jolcyna]

Hi Guys,

I am hoping to get some suggestions regarding a surprising behaviour I observed in my report. Try to explain.

1. I have a measure that calculates the revenue forecast (based on CAGR and and some historical values) - called [NonLinear_3YEARS_GTER_ForecastAnalysis_AllSLs]

2. There's a table with years called "Year_Impact_ Anaysis" that I use in charts/formuals etc. (kind of "lookuptable"). All dependant table are related with this one based on "Year" column (formatted as "Whole number") .

3. For thw purpose of using date functions in some new measure that I wan to implement, I have added a newcolumn to "Year_Impact_ Anaysis" table formatted as DATE. After adding this column - all data on chart, using "Year_Impact_ Anaysis[Year]" column in x-axis dissapears. The chart hold the revenue forecast formula that inside usues ONLY Year_Impact_ Anaysis[Year] and it's acctually a measure combining CALCULATE & FILTER called below Filter_TEST.

Filter_TEST=CALCULATE([NonLinear_3YEARS_GTER_ForecastAnalysis_AllSLs], FILTER (Year_Impact_Analysis,Year_Impact_Analysis[Year]>=2028))

4. The nested measure [NonLinear_3YEARS_GTER_ForecastAnalysis_AllSLs] alone works well - showing just unfiltered values, however the one Filter_TEST after I have added this "Year_Impact_Analysis[Date]" column to Year_Impact_Analysis is not displaying the data.

5. When I remove the "Year_Impact_Analysis'[Date]", both [Filter_TEST]and  [NonLinear_3YEARS_GTER_ForecastAnalysis_AllSLs] works well. 

As mentioned - I am not using yet anywhere "Year_Impact_Analysis[Date]" column, in any exisitng fucntions etc. No clue why adding a column to "Year_Impact_Analysis'[Date]" causes that FILTER stops working. Any idea? I would be really greatful for any sueggestions/clues.

I have noticed that data format of Year_Impact_Analysis'[Date] probably does not matter as when I changed it form Date to Text that FILTER also do not work. So the question is, why additiona of additional table to my simple table with years, blocks FILTERing? It's the first time I see it.

Before adding "Year_Impact_Analysis'[Date]" column (formatted as "Date")

After addition of "Year_Impact_Analysis'[Date]" column (formatted as "Date")

 

 

@calculate @filter

[v-yangliu-msft]

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.

[Jolcyna]

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] = 

LASTNONBLANK (

    ALL(Year_Impact_Analysis[Year] ),

    CALCULATE ( SUM (ImpactAnalysis[Sales] ) ))

Do you see anything in here that could interfere with a new column added to Year_Impact_Analysis table?