Forum Discussion

georgec96's avatar
georgec96
Icon for Helper II rankHelper II
4 years ago
Solved

Previous Row on Grouped data

Hi guys,    I'm having trouble writing a DAX formula that would give me the previous row for a dataset.   Basically I have a table with the following columns : date, user, type, category, and tim...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi georgec96 ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a column.

    Previous transaction date = 
var currentDate =A[Transaction date]
var currentCustomer =A[client]
var currentProduct = A[product_name]
return
CALCULATE(MAX(A[Transaction date]),
    FILTER(ALL(A),
        A[Transaction date] < currentDate
        && A[client] = currentCustomer
	&& A[product_name] = currentProduct
    )
)

    If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

georgec96's avatar
georgec96
Icon for Helper II rankHelper II
4 years ago

Hi Anonymous , my date column is a date/time and now the data set increased to few million rows this seems not to work anymore, keeps spinning forever is there any way to get around this?

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi georgec96 
I did a small modification on Anonymous formula. Please try

Previous transaction date =
VAR CurrentDate = A[Transaction date]
VAR T1 =
    CALCULATETABLE ( A, ALLEXCEPT ( A, A[client], A[product_name] ) )
VAR T2 =
    FILTER ( T1, A[Transaction date] < CurrentDate )
RETURN
    MAXX ( T2, A[Transaction date] )