topic Re: Previous Row on Grouped data in DAX Commands and Tips

Previous Row on Grouped data

georgec96 — Sat, 16 Apr 2022 15:19:31 GMT

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.

Re: Previous Row on Grouped data

tamerj1 — Sat, 16 Apr 2022 15:40:26 GMT

georgec96

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

Re: Previous Row on Grouped data

SpartaBI — Mon, 18 Apr 2022 08:53:32 GMT

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

Re: Previous Row on Grouped data

SpartaBI — Mon, 18 Apr 2022 08:53:47 GMT

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

Re: Previous Row on Grouped data

Anonymous — Tue, 19 Apr 2022 05:36:01 GMT

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.

Re: Previous Row on Grouped data

georgec96 — Fri, 16 Sep 2022 11:55:31 GMT

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?

Re: Previous Row on Grouped data

tamerj1 — Fri, 16 Sep 2022 12:03:57 GMT

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] )