cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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.
JKyears.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Supplier
Solution Supplier

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

Hi @j_kost 

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.




Feel free to connect with me:
LinkedIn

View solution in original post

8 REPLIES 8
Highlighted
Microsoft
Microsoft

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

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)

Highlighted
Super User VI
Super User VI

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

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

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

Highlighted
Frequent Visitor

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

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

Highlighted
Microsoft
Microsoft

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

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

Highlighted
Frequent Visitor

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

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?

Highlighted
Solution Supplier
Solution Supplier

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

Hi @j_kost 

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.




Feel free to connect with me:
LinkedIn

View solution in original post

Highlighted
Microsoft
Microsoft

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

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 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors