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"

     

Einomi's avatar
Einomi
Helper V
4 years ago

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