General Comments

[GT1PowerBi]

Hi

I have the below data, and i need a custom column to calculate the previous days value based on the "product" and "NewOrExistingMember" filter. There are multiple Products and also multiple "NewOrExistingMember" selections

Any help would be much appreciated!

🙂

[v-caliao-msft · Accepted solution]

@GT1PowerBi,

In DAX you can’t reference the previous row in any way because there is no order to the rows, So fi you need to get previous row, you can create a rank column, and then use LOOKUPVALUE function to get it.

Sample data and DAX for you reference.

Rank =
RANKX (
    FILTER (
        Table1,
        Table1[Group] = EARLIER ( Table1[Group] )
            && Table1[Group2] = EARLIER ( Table1[Group2] )
    ),
    Table1[Amount],
    ,
    ASC
)

PreviousValue =
LOOKUPVALUE (
    Table1[Amount],
    Table1[Group], Table1[Group],
    Table1[Group2], Table1[Group2],
    Table1[Rank], Table1[Rank] - 1
)

Regards,

Charlie Liao