Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Smauro
Solution Sage
Solution Sage

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.




Feel free to connect with me:
LinkedIn

View solution in original post

8 REPLIES 8
Smauro
Solution Sage
Solution Sage

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.




Feel free to connect with me:
LinkedIn

mahoneypat
Employee
Employee

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! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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
Employee
Employee

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
Not applicable

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

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
Not applicable

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?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors