Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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?
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] )
Can you please share some sample data and mimic the expected results?