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

next year

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)
Advertisements

Written by SharePoint Librarian

I'm a SharePoint Business Analyst and Jayhawk from the Kansas City Area.

3 comments

  1. 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!

    1. Hi Jelmer,

      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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.