How to get previous row values by grouping two columns in dax

[Gokulg]

Hi,

From same Table(TableA) group two columns(Column1 and Column2) and get previous row value(from Column3) in a new custom column in dax

Example

Column1	Column2	Column3	Previous Row value
A	Value1	01/02/2021
A	Value1	02/02/2021	01/02/2021
A	Value1	03/02/2021	02/02/2021
A	Value2	05/02/2021
A	Value2	05/02/2021	05/02/2021
A	Value2	04/02/2021	05/02/2021
B	Value1	01/02/2021
B	Value1	01/02/2021	01/02/2021

This need to be done in dax code (Column3 is custom created in dax). Please suggest

[Gokulg]

Hi @Jihwan_Kim 

The reason why im adding Index in dax is,

Actually the TableA which i mentioned above is not same, the real table have more records and the rows are shuffled when imported(not same order as mentioned above). Also there is a Created Date column, we need to sort the data ASC.

When i try to sort the record from Query Editor like (Sort by Column1, sort by Column2 and sort by Created Date column) and then i created Index starts from 1 in Power Query editor. So when it comes to Visualization, the sort is not applied there. So when i try to sort in Visual by same order(Sort by Column1, sort by Column2 and sort by Created Date column) the Index value is not in sequence(not in order) like some of the Index values are shuffled. So the Previous Row Value CC is not proper.

So if we create Index in Dax after Sorting above columns, the Previous Row Value CC will be proper. Please give me your suggession on creating Index in dax or anyother ways to achieve this?

[Gokulg]

Hi @Jihwan_Kim ,

Thankyou for your help. One issue im facing to add Table index in dax, i used below code

Index =
RANKX(FILTER('TableA', EARLIER('TableA'[Column1]) = 'TableA'[Column1]
&& EARLIER('TableA'[Column2]) = 'TableA'[Column2]),
'TableA'[Column3],,DESC,Skip)

Here Column3 value is in date with time format (like 01/02/2021 17:55), so if both rows have same date with time im getting index as duplicate number

Example

Column3                  Index
01/02/2021 17:54    1
01/02/2021 17:55    2
01/02/2021 17:55    2
01/02/2021 17:56    3

Please help me to get unique row index in dax

[Jihwan_Kim]

Hi, @Gokulg 

Thank you for your feedback. I got an index column in Power Query Editor because I could not differentiate the below, and I could not understand the logic of the sequence. So I decided to choose the easiest way to create an index column which is doing it in Power Query Editor.

A

Value2

05/02/2021

B

Value1

01/02/2021

[Jihwan_Kim · Accepted solution]

Hi, @Gokulg 

Please check the below for creating a new column.

Previous Row Value CC =
VAR currentindex = 'Table'[Index]
RETURN
CALCULATE (
MAX ( 'Table'[Column3] ),
FILTER (
'Table',
EARLIER ( 'Table'[Column1] ) = 'Table'[Column1]
&& EARLIER ( 'Table'[Column2] ) = 'Table'[Column2]
&& 'Table'[Index] = currentindex - 1
)
)

https://www.dropbox.com/s/t4yt3utpyj9t2u9/gokulg.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[mbahonen]

Hello,

I know this is a bit old, but your solution works mostly great for something I'm working on, however, I'm wondering if there is a way to modify this so that instead of a blank, you actually get the previous row's value in the new column. So far I've been unable to do this.

ETA: Nevermind. I was able to get this by deleting the below line from the code:

&& EARLIER ( 'Table'[Column2] ) = 'Table'[Column2]