Comparing values in same column in Power BI

[JH-PowerBI-1]

Hello.

I have a  table A with tw columns: Person and Dates.  I have already sorted the data first by Person and then by Dates in descending order:

Column Person      Column Dates

3                             06/01/2021

3                             05/30/2021

2                             04/30/2021

2                             02/13/2021

1                             07/21/2021

1                             07/15/2021 

Each value in the Person column contains two correspondinig values in the Dates columns.  I need to compare the two dates for each person in the Person column. 

I would like to create a new column Most Recent Date whose values are as follows:

The first row should have a 1 if the date on the first row of column Dates  is greater than the date on the second row of column Dates.

The second row should be blank.

The third row should have a 1 if the date on the third row of column Dates  is greater than the date on the fourth row of column Dates.

The fourth row should be blank.

The fifth row should have a 1 if the date on the fifth row of column Dates  is greater than the date on the sixth row of column Dates.

The sixth row should be blank.

And so on.

This is what the column I want to create should look like:

Most Recent Date

1

0

1

0

1

0

So table A should look like this:

Column Person      Column Dates     Most Recent Date

3                             06/01/2021              1

3                             05/30/2021              0

2                             04/30/2021              1

2                             02/13/2021               0

1                             07/21/2021               1

1                             07/15/2021               0 

My objective is to create a new table B containing only the rows of table A that have a 1 in the Most Recent Date column of table A (I already know how to do this).  So table B should look like this:

Column Person      Column Dates     Most Recent Date

3                             06/01/2021              1

2                             04/30/2021              1

1                             07/21/2021              1

Any help with be greatly appreciated.

Thank you!

[Syndicate_Admin]

Best regards... very complicated... But manage to solve with this explanation. I'm really being as a template. I've used it a lot of times, and since I have one date next to it, I can even set a END_DATE and START_DATE, which helps me calculate other types of data.

[v-kkf-msft]

Hi @JH-PowerBI-1 ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[TomMartens]

Hey @JH-PowerBI-1 ,

what you are looking for is not as simple as we might want, please allow me to direct you to this blog, here I used more words: Navigating Tabular Tables - Previous Row - Mincing Data - Gain Insight from Data (minceddata.info)

Next to that, you may want to rethink your 1 and 0 approach, my approach sorts the rows inside a group (Column Person) Descending, meaning the most recent row is flagged with 1. This provides the same possibilities, filtering for all rows that contain the value 1.

Here we go ...

The data, (please be aware that the date of the first row represents the 6th of January and not the 1st of June 🙂 )

It's mandatory that the data type of the column Column Dates is Date 

 

Then I group the rows by the column Column person and select the Operation All Rows

Then it becomes a little hacky as it is necessary to tweak the Group By transformation in the Advanced Editor, doing that will prevent opening the Group By dialog once again, as the tweak can not be rendered by the dialog.

Nevertheless here we go ...

In the advanced editor I identify the Group By transformation:

Then I get rid of some not needed code

Then I replace the _ (the underscore) with some code. The underscore represents a table. The object represented by the underscore is depending on a context, here it is the table created by the Group By transformation.

The code that I use to replace the _ is this:

Table.AddIndexColumn (Table.Sort( _ , {{"Column Dates" , Order.Descending}}) , "Most Recent Date" , 1 , 1)

the complete line including the remaining part ...

each Table.AddIndexColumn (Table.Sort( _ , {{"Column Dates" , Order.Descending}}) , "Most Recent Date" , 1 , 1), type table }}

The the table can be expanded:

And the result, now the most recent date for each person is flagged with the value one and can be used to filter the row:

Hopefully, this is what you are looking for.

Regards,

Tom

[Syndicate_Admin]

Best regards! interesting... I managed to do it and it serves as a Date Ranking.

I mean, I use the DAX RANKX to create a Ranking of dates and know which is the first and last date of a group of users, for example I could have the last start date of session and the first, or play with that ...

But my problem lies in this:
I need to know per user the number of days of difference between the date RankX 1 and RankX 2. But the dates are in the same column, one below the other.

Do you have any idea how to approach that please?

[TomMartens]

Hey @JH-PowerBI-1 ,

please excuse, but I have to admit that I'm not able what you are trying to achieve. Please consider to create pbix that contains sample data, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[JH-PowerBI-1]

Hi Tom.

I added more detail, including what my original table looks like, what it should look like, and what the new table I want to create should look like.  Thank you very much for your help!