Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 l...
Need Help
  • Smauro's avatar
    Smauro
    6 years ago

    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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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