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 "NewOrExistingMember" selections

 

 

Any help would be much appreciated!

:-)

 

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

     

1 Reply

  • 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