How can I set blank value when selected year is equals to the min forecast previous year

[samudralb]

I have a table like this.

I need to write DAX query based on this logic.

If I select 2022 as Forecast Year Filter it should show 'Forecast Previous year from 2023'. not from 2022. 

If I select 2023 as Forecast Year Filter it should show 'Forecast Previous year from 2024'. not from 2023.
 

Exisiting DAX code is :

Forecast Previous Year=

VAR ForecastYear =

SELECTEDVALUE(

    'Forecast Year'[Forecast Year],

    CALCULATE(

        MAX( 'factForecast'[Forecast Year] ),

        REMOVEFILTERS( 'factForecast' )

    )

)-1

RETURN

CALCULATE(

    SUM( factForecast[Amount] ),

    'Forecast Year'[Forecast Year] = ForecastYear

)

Appreciate if some one can support on this

Thank you.

[Anonymous · Accepted solution]

Hi @samudralb 

Please try this:

First of all, I create a set of sample data:

Then add a new table with the year column:

Table 2 = SELECTCOLUMNS('Table',"Year1",'Table'[Year])

A measure can be created:

MEASURE =
VAR _currentYear =
    MAX ( 'Table 2'[Year1] )
RETURN
    IF ( MAX ( 'Table'[Year] ) >= _currentYear + 1, SUM ( 'Table'[Value] ) )

Then create a slicer and a table:

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samudralb]

Thank you so much 🙂