Power BI: Calculate next year’s amount in previous year’s row

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.

You need:

• A number-formatted column for [Year]
• A number-formatted calculated column for [NextYear] (you can hide once created)

  • NextYear='Table'[Year]+1

• Values for each year ([This year] above)

1. Create a calculated column
2. 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)

3. 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)