Forum Discussion
Sales Table (Need Only Last 4 Years) Help/Question
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"
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.
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"
Thank you that worked as expected and kept the M Code query native!
THanks for the help
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.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"You're very welcome, IamTDR.
I'm happy to hear that it worked as a solution for you! 🙂