The following is a DAX formula you can use to create a calculated column that shows “next year’s” value in “this year’s” row. You can easily adapt this to show “yesterday’s” amount or “tomorrow’s” total as well. It can be modified for days, weeks, months, etc. as long as the time measure is able to be sorted sequentially.
- A number-formatted column for [Year]
- A number-formatted calculated column for [NextYear] (you can hide once created)
- Values for each year ([This year] above)
- Create a calculated column
- Paste the following formula in the formula bar, changing sheet name and column names as appropriate:
NextYearCount = var previous = CALCULATE ( FIRSTNONBLANK ('Table'[This year],""), FILTER ( 'Table','Table'[Year] >EARLIER('Table'[Year]) && 'Transaction'[Year] = EARLIER('Transaction'[NextYear]) ) ) return IF (ISBLANK(previous), BLANK(), previous)
- Press “enter” or click the “check” to save the formula
Note: I believe if your numbers only ever increase, you don’t need [NextYear] as an added column and can use this lighter formula. If your numbers vary up and down, you’ll need the formula above for the values to be correct.
NextYearCount = var previous = CALCULATE ( FIRSTNONBLANK ('Table'[This year],""), FILTER ( 'Table','Table'[Year] >EARLIER('Table'[Year]) ) ) return IF (ISBLANK(previous), BLANK(), previous)
3 Replies to “Power BI: Calculate next year’s amount in previous year’s row”
Hi Nate, love what you’ve done there. For me it only works on increasing values (so your second statement). What is the “Transaction” table in the first statement? Thanks!
That statement looks at sequential data, pulling the next data point in a series into the current row. In that specific case, it’s pulling 2018’s total into 2017’s row so we can then do calculations using the difference.
Sorry, so where do you create ‘Transaction'[Year] and what’s in that table?