Forum Discussion

GT1PowerBi's avatar
GT1PowerBi
New Member
8 years ago
Solved

General Comments

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 "N...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    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

     

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
8 years ago

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