Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |