Last 3 years from table that's not a date table.

[Anonymous]

Hi Power Query heroes!

My organisation has a table as attached. I would like to create a cloumn to filter my reports for the last 3 'admission' years.

I was thinking of something along the lines of if [Is Current Admission Year] = Y then list {[Academic Year], [Year Minus 1], [Year Minus 2]} of that row to every row.

Then a column to check if the academic year is equal to any value in the list, so only 2020,2019,2018 would result in true dynamically.

How can I apply the result of the first if statement to every row?

Thanks in advance for your suggestions.
JK

[Smauro · Accepted solution]

Hi @Anonymous 

Fixing a little bit @artemus' code:

    CurrYear = PreviousStep{[Is Current Admissions Year Flag = "Y"]}[Academic Year],
    LastThree = Table.AddColumn(PreviousStep, "Is in last 3", each 0 <= (CurrYear - [Academic Year]) and (CurrYear - [Academic Year]) < 3, Logical.Type)

First, computing Current Academic Year only once --> less load

Second, calling that to create a true/false column.

I wasn't sure if you want current year included or not. If not, you should change the constraints.

Then, you can use this T/F column however you like. Since CurrYear is dynamic, it will change when data changes.

[mahoneypat]

You could do this in query, but it is probably easier with a DAX calculated column with a formula like this, replacing with the Table[Column] for your actual Date column.

Admission Year = Year(Sales[SaleDate]) - Year(MAX(Sales[SaleDate]))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks for taking ther time to offer a suggestion Pat. Unfortunately this currently needs to be in Power Query for a table as described. I don't have a "date" to call upon in this way.

Best wishes
JK

[artemus]

Assuming that [Is Current Admission Year Flag] is unique in the table you can just grab that row at any time (and there is no future year in the table).

Like:

= PreviousStep{[Is Current Admission Year Flag = "Y"]}[Academic Year] - [Acedemic Year] <= 3

 Where PreviousStep is the name of your Previous step (e.g. Source if it is the first step)

[Anonymous]

Thanks Artemus.

When I try the following:

#"Last three" = Table.AddColumn(#"Changed Type", "Counting Years", each {[Is Current Admissions Year Flag]="Y"}[Academic Year]-[Academic Year]<=3)

There is an error, cannot apply field access to to type list. Am I missing something?

Also you mention "no future years in the table". For which there is. The flag will always be one row up from the bottom if that makes a difference?

Thanks
JK

[artemus]

Opps, you are right, I made a typo:

#"Last three" = Table.AddColumn(#"Changed Type", "Counting Years", each PreviousStep{[Is Current Admissions Year Flag="Y"]}[Academic Year]-[Academic Year]<=3)

 Here is what is should be

[Anonymous]

Syntactically I can get a result but this is True,False.

I basically need to go to the row where [Is Current Admissions Year] = "Y" (will change each year), then get the years required, either by calculation or refering to the coumns that host them, [Academic Year], [Year Minus 1], [Year Minus 2]. Place these specific values in a new column in each row.

From there I can check if the academic year for a row matches any value in the new column to use as a filter later.

Does that make sense?

[artemus]

Sure that is easy, just remove the last part of the formula:

#"Last three" = Table.AddColumn(#"Changed Type", "Counting Years", each PreviousStep{[Is Current Admissions Year Flag="Y"]}

Then do a column expansion