How to calculate for next year without using year in table display

[Jtbonner1986]

Hi

I am bing really silly here, but i want to create a DAX measure to calcualte demand for 'next year' and 'next year +1' etc without using the field 'year' in the table visual. 

The suporting table is structured like the below

Material	Sales Date	Total
A	01/01/2023	10
A	01/01/2024	10
A	01/01/2025	10
A	05/01/2023	10

the output i desire is like the below (and i want to create this without hardcoding the year in the measure) 

Material	Next Year Sales (2024)	Next Year +1 Sales (2025)
A
B

So i currently use something like this:

Calcaulate(Sum(Material[total]),Calendar_Table[year] = "2024"))

but i want to avoid this for future proofing the report 

Cheers

[some_bih]

Hi @Jtbonner1986 

In Calendar / Date table create calculated column NextTwoYears, with True False values.

In your measure, CALCULATE part should look like below (adjust your Calendar / Date table name)

Calculate(Sum(Material[total]), <YourTable>[NextTwoYears]="True"

NextTwoYears =

VAR __current_year =

    YEAR ( TODAY () )

VAR __next_year = __current_year + 1

VAR __two_years = __current_year + 2

VAR __Result =

    IF (

        YEAR ( 'Date'[Date] ) = __next_year,

        TRUE (),

        IF ( YEAR ( 'Date'[Date] ) = __two_years, TRUE (), FALSE () )

    )

RETURN

    __Result

Did I answer your question? Kudos appreciated / accept solution!

[ChiragGarg2512 · Accepted solution]

@Jtbonner1986 , Use this dax measure formula:

Next Year Sales (2024) = Calcaulate(Sum(Material[total]),Calendar_Table[year] = Year(Today()) + 1)) 

and +2 for next year +1

Thank You