Latest Date Financial Year

[Mat42]

This feels both simple and probably not possible all at the same time.

So, this is basically my dataset:

I used the following M to create the Latest column, which displays a 1 if the date is the latest for each Code:

if [Date] = List.Max(let currentCode = [Code]
in Table.SelectRows(#"Renamed Columns",
each [Code] = currentCode)[Date])
then 1
else 0

And it works. But now they've decided they want a 1 alongside every date that is the latest date for each financial year, so the dataset would look like this:

To demonstrate: for code 11111 there is a 1 alongside 02/03/2024 because it is the latest date in financial year 2023-2024 and alongside 01/05/2024 because it is the latest date in financial year 2024-2025. Both entries for 33333 have a 1 because each entry is the latest (and only) date in separate financial years.

They want this done in Power Query for reasons they haven't yet told me.

Is this possible? I don't mind adding in new columns or merging tables if I need to.

[FreemanZ]

hi @Mat42 ,

try like:

if 

[Date] = 

List.Max(

    let currentCode = [Code], currentyear = [FinYear]

    in Table.SelectRows(

       #"Renamed Columns",

        each [Code] = currentCode and each [FinYear] = currentyear )[Date]

)

then 1

else 0

[Mat42]

Thanks for replying.

The code is getting flagged at this point:

Token Literal expected

[Angith_Nair]

Hi @Mat42 

Create a financial year column that identifies the financial year for each Date.

AddFinancialYear = Table.AddColumn(#"Renamed Columns", "FinancialYear", each if Date.Month([Date]) >= 4 then Date.Year([Date]) + 1 else Date.Year([Date]), Int64.Type)

Modify the logic as per the financial year:

AddLatestFlag = Table.AddColumn(AddFinancialYear, "Latest", each 
    if [Date] = List.Max(
        Table.SelectRows(AddFinancialYear, each [Code] = [Code] and [FinancialYear] = [FinancialYear])[Date]
    ) then 1
    else 0)

[Mat42]

Hi, thanks.

I tried this and I've ended up like this (this is the actual data):

I'd expect a 1 to be alongside 10000215136 and 1000025230 because they are the latest date for each financial year because they are the only entries, and for 1000025429 there should be a 1 against 21/002/2024 because that is the latest date in financial year 2023/2024 and a 1 against 19/06/2024 because it is the latest date in 2024/2025.

[v-xingshen-msft · Accepted solution]

Hi @Angith_Nair ,
According to your question, we have tried other methods, based on your actual data, I hope to help you with your question!
Our first step is to find the maximum date under each fiscal year, which we call MAXTable.

Then we merger with the original table to get a MaxDate, which is the maximum date value for each fiscal year.

And finally, we add a new column to find out if each ID is the latest date.

I hope this helps you solve your problem, if you have further questions you can check out the uploaded pbix, I'd be incredibly proud to solve your query!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Mat42]

You are awesome!

Although this didn't quite do what I needed because it gave me the latest date rather than the latest financial date, I actually removed a financial year column from the dataset, so I used that instead of 'Year' for the grouping and it worked great. 

Thanks!