Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
@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/3395....
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.
@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/3395....
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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |