Previous Row on Grouped data

[georgec96]

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 time of transaction. I need to group my data by user, date, type category and then get the previous time of transaction for each user.

I believe this would be similiar to a PARTITION BY in SQL however I'm not sure how to achieve this in DAX.

Any help would be greatly appreciated.

[v-rongtiep-msft · Accepted solution]

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]

Hi @v-rongtiep-msft , 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]

Hi @georgec96 
I did a small modification on @v-rongtiep-msft 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] )

[SpartaBI]

Previuos Time Per User =

VAR _current_time = MAX(PreviousTime[time])

VAR _table =

CALCULATETABLE(

FILTER(

PreviousTime,

PreviousTime[time] < _current_time

),

REMOVEFILTERS(PreviousTime),

VALUES(PreviousTime[Date]),

VALUES(PreviousTime[user])

)

VAR _result =

MAXX(_table, PreviousTime[time])

RETURN

_result

This version will return blank in case there weren't any rows with time before (as in the first row in that day for that user). If you want this to show the previous time also when it was in the previous date than best to add another column with date and time and do the calculation of the max and previous on it. In this case you need to remove the VALUES(PreviousTime[Date]) from the measure I wrote

[SpartaBI]

Previuos Time Per User =

VAR _current_time = MAX(PreviousTime[time])

VAR _table =

CALCULATETABLE(

FILTER(

PreviousTime,

PreviousTime[time] < _current_time

),

REMOVEFILTERS(PreviousTime),

VALUES(PreviousTime[Date]),

VALUES(PreviousTime[user])

)

VAR _result =

MAXX(_table, PreviousTime[time])

RETURN

_result

This version will return blank in case there weren't any rows with time before (as in the first row in that day for that user). If you want this to show the previous time also when it was in the previous date than best to add another column with date and time and do the calculation of the max and previous on it. In this case you need to remove the VALUES(PreviousTime[Date]) from the measure I wrote

[tamerj1]

@georgec96 

Can you please share some sample data and mimic the expected results?