Grouping years with dax for forecast

[Anonymous]

Hi,

I need to group my forecast count for all years before 2021 into the 2021 bucket. and then I will need to use this measure in a bar chart. 

So basically in the sample below I need to sum the values between 2015 - 2021 as 2021.

Year	Forecast
2015	5
2016	3
2017	4
2018	5
2019	1
2020	3
2021	5
2022	6
2023	8

Is this possible with dax?

Thank you

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

I write a measure for your reference, 

Total before 2021 =
VAR _startYear =
    CALCULATE ( MIN ( 'Table'[Year] ), ALL ( 'Table' ) )
VAR _thisYear =
    YEAR ( TODAY () )
RETURN
    IF (
        MIN ( 'Table'[Year] ) = _thisYear,
        CALCULATE (
            SUM ( 'Table'[Forecast] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Year] <= _thisYear
                    && 'Table'[Year] >= _startYear
            )
        )
    )

result

if you need more help, please @ me.

Best Regards,

Community Support Team _Tang

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

[MFelix]

Hi @Anonymous 

Try the following measure:

Forecast = CALCULATE (SUM(Table[Forecast), FILTER(ALL(Table[Year]), Table[Year] >= 2015 && Table[Year] <= 2021))

This may need some adjustments depending on yout model setup.