topic Re: How can I set blank value when selected year is equals to the min forecast previous year in DAX Commands and Tips

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

samudralb — Thu, 07 Mar 2024 11:26:24 GMT

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 — Thu, 07 Mar 2024 02:32:01 GMT

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 — Thu, 07 Mar 2024 11:25:31 GMT

Thank you so much 🙂