topic DAX function to get previous row value grouped by another column in DAX Commands and Tips

DAX function to get previous row value grouped by another column

luckygirl — Tue, 14 Jan 2025 19:35:32 GMT

Hi,

I have a table like below.

ClientCode	CompletedDate	ProductDetail
1	1/01/2018	A
1	1/04/2019	B
1	23/05/2021	C
2	6/05/2022	D
2	9/02/2018	A
3	5/05/2019	C

I want to get the previous value of the "ProductDetail" column ordered by the "CompletedDate" and grouped by "Code". So the expected output is below.

ClientCode	CompletedDate	ProductDetail	PreviousProductDetail
1	1/01/2018	A	No
1	1/04/2019	B	A
1	23/05/2021	C	B
2	9/02/2018	A	No
2	6/05/2022	D	A
3	5/05/2019	C	No

I am a beginner to dax and can't figure out the method. Thanks for the support.

Re: DAX function to get previous row value grouped by another column

Greg_Deckler — Tue, 14 Jan 2025 20:31:30 GMT

luckygirl Try this:

Previous Product Detail Column = VAR __Code = [ClientCode] VAR __Date = [CompletedDate] VAR __Table = FILTER( ALL( 'Table' ), [ClientCode] = __Code && [CompletedDate] < __Date ) VAR __PrevDate = MAXX( __Table, [CompletedDate] ) VAR __Result = MAXX( FILTER( __Table, [CompletedDate] = __PrevDate ), [ProductDetail] ) RETURN __Result or Previous Product Detail Measure = VAR __Code = MAX( 'Table'[ClientCode] ) VAR __Date = MAX( 'Table'[CompletedDate] ) VAR __Table = FILTER( ALL( 'Table' ), [ClientCode] = __Code && [CompletedDate] < __Date ) VAR __PrevDate = MAXX( __Table, [CompletedDate] ) VAR __Result = MAXX( FILTER( __Table, [CompletedDate] = __PrevDate ), [ProductDetail] ) RETURN __Result

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
( __Current - __Previous ) * 1.

Re: DAX function to get previous row value grouped by another column

luckygirl — Tue, 14 Jan 2025 20:36:36 GMT

Greg_Deckler Thank you so much!