Forum Discussion

Mat42's avatar
Mat42
Resolver I
1 year ago
Solved

Latest Date Financial Year

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 ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

FreemanZ's avatar
FreemanZ
Super User
1 year ago

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's avatar
    Mat42
    Resolver I
    1 year ago

    Thanks for replying.

     

    The code is getting flagged at this point:

     

    Token Literal expected