Max date based on another column

Hi Folks,

I have an issue with a query.

Scenario
We get a daily file where it gives us the details of individual records for the last 90 days regardless of whether the data in the records have changed or not. So I could potentially have the same identical record 90 times with the only differentiator being the date of the file (which is the date the file is received on).

We had thought to use a calculated column but we are going to deal with a LOT of data so figured this would be expensive performance wise.

I have created a Unique Key which identifies the individual record (Unique Identifier for Max Date) and I also have a File Date column. Iā€™d love to be able to create a column through M which identifies the Max Date of the Unique Identifier for Max Date and returns ā€œMax Dateā€ for the latest date and ā€œPrevious Recordā€ for the other values. Iā€™ve posted, hopefully correctly, a picture below of some of the columns Iā€™m dealing with. Iā€™m struggling with the M code, hopefully Iā€™ve mapped out the issue clearly.

Hi @DavieJoe,

Can you go over this thread (post #8) - think it might help you with what youā€™ve described.

Otherwise just create some dummy data, doesnā€™t matter what it is as long as the structure- and data types are the same as your actual source. Provide some additional context so members can better assist.

1 Like

Many thanks Melissa, will check this out and get back to you :grinning:

Hi Melissa,

If Iā€™ve understood that example correctly, youā€™re creating a parameter that provides a Max File date created the most recent file date.

That wouldnā€™t work for me as not all records will exist in the most recent file as each daily file only contains data for the last 90 days. Iā€™ll have records that will drop off the daily file but still need to have Max Record Date applied against them as we will have them in a big history table.

I merge the Code, Reference & Date of Event columns to create my Unique Identifier for Max Date column.

Unique Identifier for Max Date column will be used to create the Output based on the File Date column.

Apologies if Iā€™m doing a terrible job of explaining this or havenā€™t included what you need.

Example Data 1.xlsx (9.7 KB)

Hi @DavieJoe,

Thanks for that sample, I have a better understanding of your requirement now.
Iā€™ll get back to you.

1 Like

Thank you Melissa, really appreciate it.

Hi @DavieJoe,

See if this works for you.

  1. Grouped the data on Code & Reference
  2. Brought back the data from the nested table
  3. Created a Custom Column for the Output

Hereā€™s your sample file. Max date based on another column.pbix (23.0 KB)
I hope this is helpful.

1 Like

@Melissa You are very fast :+1:

1 Like

Fast & awesome @MudassirAli

3 Likes

:innocent:

1 Like

@Melissa Thank you so much, it had been annoying me, I knew it was possible I just didnā€™t work out the steps to do it. I had tried using Group By but that was after creating a duplicate query and merging them back together and matching the Reference and then a conditional column.

Your way is far slicker, thank you for taking the time out to help and for turning it around so quickly.

Great learning for me!

David

1 Like

Glad I could help @DavieJoe
:+1:

1 Like

@Melissa Iā€™m new to PowerBI and am thankful for your post on how to solve this Max Date based on another column.
I stumbled at your step 3 and kept getting an error that said couldnā€™t recognise ā€˜ExpandDataā€™. I have worked out another way to add the output column - by using ā€˜Add Conditional Columnā€™ from the Add Columns tab.

1 Like

Hi @barryM

this would be best answered by creating a new post as this is solved.

Thanks

David

1 Like