Summing a measure, but for a Year + 1

[shinney]

Hello,

I'm trying to have 2 columns. One for the budget for current year, and one for the budget of next year. So if I set Year = 2021, I want a column with 2021 numbers and 2022 numbers. Eventually, I want to have a 3rd column that's the total for current year + next year, but I'm having difficulties getting the correct DAX formula for the next year's budget to apply.

There are 2 tables involved, a Year table, and a Budgets table. I decided to create a new column [Year # Future = Year + 1] in the Year table to simplify things.

I so far have:

Next Year Budget =

VAR vYear = SELECTEDVALUE (YearTable[Year # Future], BLANK())

VAR vResult =

CALCULATE(

    SUM(BudgetTable[BudgetAmount]),

    FILTER(

        (YearTable),

        YearTable[Year # Future] = vYear)

)

RETURN

vResult


Problem is that the result is still showing this year's number. I made sure the new column 'Year # Future' gave me the next year's number.... What am I doing wrong?

[v-tangjie-msft]

Hi @shinney ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create calculated columns.

Year # Future = 'BudgetAmount'[Year]+1

Next Year Budget = CALCULATE(max('BudgetAmount'[Budget]),FILTER('BudgetAmount','BudgetAmount'[Year]=EARLIER(BudgetAmount[Year # Future])))

current year and next year budget = 'BudgetAmount'[Budget]+'BudgetAmount'[Next Year Budget]

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[amitchandak]

@shinney , Have a separate year or date table joined with your table

example

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[shinney]

I have also tried the following DAX, but this did not work either:

VAR vYear = IFERROR( VALUES (YearTable[Year # Future]), Blank())

RETURN

    CALCULATE (Sum(BudgetTable[BudgetAmount]), YearTable[Year # Future] = vYear)