Forum Discussion

IamTDR's avatar
IamTDR
Responsive Resident
4 years ago
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...
Need Help
Show and Tell
Tips and Tricks
  • Knighthawk's avatar
    Knighthawk
    4 years ago

    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"

     

Knighthawk's avatar
Knighthawk
Helper I
4 years ago

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's avatar
IamTDR
Responsive Resident
4 years ago

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.

  • IamTDR's avatar
    IamTDR
    Responsive Resident
    4 years ago

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

    THanks for the help

  • Knighthawk's avatar
    Knighthawk
    Helper I
    4 years ago

    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"

     

  • Knighthawk's avatar
    Knighthawk
    Helper I
    4 years ago

    You're very welcome, IamTDR.

     

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