Calculate last or previous value within Power BI

Here is how to calculate last, previous, or values other periods ago in Power BI using DAX measures. It is not always straightforward, and there are multiple approaches to how to do that.

Here is a sample of my data. You can download the pbix file a the end of this post.

Calculate last or previous value in Power BI based on the date

This is the easiest scenario because there are specialized DAX date functions that can help, and you can do additional calculations if necessary. There might be difficulties if you don’t have value for every date, but in that case, the next technique will help.

Calculate the last value with a measure

There is a lot of possibilities for how to do that. Most of them by using the advantages of DAX time intelligence functions.

Here is an example with LASTDATE.

_last value LASTDATE = 
CALCULATE (
    SUM ( MyTable[Value] ),
    FILTER ( MyTable, MyTable[Date] = LASTDATE ( ALLSELECTED ( MyTable[Date] ) ) )
)

Here is an example with LASTNONBLANK.

_last value LASTNONBLANK = 
CALCULATE (
    SUM ( MyTable[Value] ),
    LASTNONBLANK ( MyTable[Date], SUM ( MyTable[Value] ) )
)

Here is an example with LASTNONBLANKVALUE.

_last value LASTNONBLANKVALUE = 
LASTNONBLANKVALUE ( MyTable[Date], SUM ( MyTable[Value] ) )

As you can see, all of them give as same results during the last value calculation.

Calculate previous value with a measure

In a situation when you have dates, you should detect the last date and then go after the previous one with PREVIOUSDAY.

_previous value PREVIOUSDAY = 
CALCULATE (
    SUM ( MyTable[Value] ),
    FILTER (
        MyTable,
        MyTable[Date] = PREVIOUSDAY ( LASTDATE ( ALLSELECTED ( MyTable[Date] ) ) )
    )
)

Calculate last or previous value in Power BI based on position

If it is possible to determine the sequence in your data by creating an index, then there are multiple ways to get the last or previous value.

I will create an index based on the date column, but there might be other parameters that might indicate sequence. To do that, I am using the technique from this post.

In my case, for each category in column Attribute, there is a unique set of dates. For each of the categories, the index looks like this.

Index = RANKX(MyTable, MyTable[Date], , ASC, Dense)

Last value measure based on positon

_last value based on position = 
CALCULATE (
    SUM ( MyTable[Value] ),
    FILTER ( MyTable, MyTable[Index] = MAX ( MyTable[Index] ) )
)

Results are matching with the previous methods.

Here is a universal method for the last value calculation if table columns have blank (missing) values.

Previous value measure based on position

In the FILTER function, I ensure that the measure will work if I would like to use it in a calculated column. It is not necessary.

_previous value based on position = 
CALCULATE (
    SUM ( MyTable[Value] ),
    FILTER (
        ALLEXCEPT ( MyTable, MyTable[Attribute] ),
        MyTable[Index]
            = MAX ( MyTable[Index] ) - 1
    )
)

Results are matching with the previous methods.

Return previous value from the column in the Power BI table

It can be done with the measure like above or with the function EARLIER.

PrevValue = 
CALCULATE (
    SUM ( MyTable[Value] ),
    FILTER (
        MyTable,
        MyTable[Index]
            = EARLIER ( MyTable[Index] ) - 1
            && MyTable[Attribute] = EARLIER ( MyTable[Attribute] )
    )
)

Alternative to EARLIER function

As an alternative to the EARLIER function, you can use variables like this. It is not easy to understand how exactly EARLIER works, and this approach might be helpful.

PrevValue2 = 
VAR var1 = MyTable[Index]
VAR var2 = MyTable[Attribute]
RETURN
    CALCULATE (
        SUM ( MyTable[Value] ),
        FILTER ( MyTable, MyTable[Index] = var1 - 1 && MyTable[Attribute] = var2 )
    )

If you like to know more about that I recommend these posts:

• Cracking DAX – the EARLIER and RANKX
• Can EARLIER be used in DAX measures

Here you can download the pbix file that contains the result.

Take a look at other DAX-related posts in this blog. Here is a good one that shows how to detect outliers in Power BI by using DAX functions.