Sales Table (Need Only Last 4 Years) Help/Question

[IamTDR]

Hi
What is the Best Practice for filtering a sales table for the last four years only.  Of course you can manually filter the table for the desired years, but what if you want to make it dynamic?  I usually make a separate query on the same table where I remove all other fields besides year, sort high to low, and then keep first four rows.  Then I merge between the two tables.
Probably a better quicker method exist, so what are others using?

[Einomi]

@Knighthawk Nice proposition

@IamTDR Maybe I can offer a slight different approach

What about creating  column date from your columns fiscal_month and fiscal_year and and let's say you call this column Date, you could apply the following M code which will give you the latest 4 years based on the data (not on the current year) so if your latest date in your data set is 2017 it will give you all the data from 2017, 2016, 2015 and 2014. Futhermore, with this M code you could adjust the fiscal period Apr - Mar

= Table.SelectRows(PREVIOUS STEP, each [Date] >= #date(Date.Year(List.Max(PREVIOUS STEP[Date],1))-4,1,1) and [Date] <= #date(Date.Year(List.Max(PREVIOUS STEP[Date],1)),12,31))

Let me know how it goes

Sorry, I updated the M code and it should work better now

[Knighthawk]

Hi IamTDR,

I have provided two sets of code that will hopefully provide what you are hoping to achieve in your filter.

The first set of code will look at the last four years to the day.  For example, if today is July 15, 2022, the code should show July 16, 2018 forward.

The second set of code will look at the last four years simply based upon the current year.  For example, if today is July 15, 2022, the code will show all dates for years 2019, 2020, 2021, and 2022.

I hope these sets of code are helpful and provide what you needed. 🙂

Code if you want it to be four years or less to the day (M Code) :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}),
    #"Inserted Age" = Table.AddColumn(#"Changed Type", "Age", each Date.From(DateTime.LocalNow()) - [Date], type duration),
    #"Calculated Total Years" = Table.TransformColumns(#"Inserted Age",{{"Age", each Duration.TotalDays(_) / 365, type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Calculated Total Years", each [Age] <= 4),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Age"})
in
    #"Removed Columns"

Code if you want it to be four years or less based upon the current year, regardless of the current day within the year (M Code) :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.Year([Date]) > Date.Year(DateTime.LocalNow()) - 4)
in
    #"Filtered Rows"

[IamTDR]

Thanks.  Option number two seems to work best for me.
The datawarehouse table I'm using does not contain a date field, only a fiscal month and fiscal year field.
So I am using the fiscal month/fiscaldate field to create a calendar date field.  Once the calendar date field is created and I change type to Date, I'm losing native query right away.  Was trying to figure out a way to keep the query native.

[Knighthawk]

Hi IamTDR,

Would you be able to provide a sample file and/or a screenshot of the formatting that comes in natively, particularly of the fiscal month/fiscaldate field that you are referring to?

[IamTDR]

 

[Knighthawk]

Hi IamTDR,

Will this work:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [fiscal_year] > Date.Year(DateTime.LocalNow()) - 4)
in
    #"Filtered Rows"

[IamTDR]

No.  It wont because the field 'fiscal_year' is seen as a number and not a date.
I usually duplicate this table, although I do not enable load, remove other fields except date field, remove duplicates, sort, and then keep first 4 rows.  Then I do a InnerJoin between the two tables.  This keeps the data being native but the results are rather slow.  Before doing this I would manually filter the table but I no longer want to revise this report yearly.  Goal is to be dynamic.

[Knighthawk]

Hi IamTDR,

Since I am still rather new to Power Query, I definitely might be missing something, or there could of course easily be differences between your real data and the test data I am creating for my test code.  However, the code I provided seems to be working with [fiscal_year] being set to type number.  In case it can help to overcome the issue between comparing number and date though, I added one additional function (Number.From) to convert the "current year - 4" to a type number (in case it was being seen as date and causing the issue you are running into) so that the logic code would be comparing type number to type number.  You could add this function around [fiscal_year] also just to be safe if you are still running into a type issue.  I hope that this helps.  Without seeing the whole picture though, maybe I am missing something.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [fiscal_year] > Number.From(Date.Year(DateTime.LocalNow())) - 4)
in
    #"Filtered Rows"

[IamTDR]

You are correct.  This in theory got the dataset to be correct and stay native. 

each [fiscal_year] > Date.Year(DateTime.LocalNow()) - 4)

Problem is that I need to focus on fiscal year (May - Apr Fiscal Year)  So in doing the above code, I receive the fiscal years 2023-2019 instead of 2023-2020.

[Knighthawk · Accepted solution]

Hi IamTDR,

This should adjust for the 8 months difference between the current date and your fiscal year:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each [fiscal_year] > Date.Year(Date.AddMonths(DateTime.LocalNow(), +8)) - 4)
in
#"Filtered Rows"

[IamTDR]

Thank you that worked as expected and kept the M Code query native!

THanks for the help

[Knighthawk]

You're very welcome, IamTDR.

I'm happy to hear that it worked as a solution for you! 🙂