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