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 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?

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"

     

12 Replies

  • Knighthawk's avatar
    Knighthawk
    Helper I
    4 years ago

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

      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's avatar
        Knighthawk
        Helper I
        4 years ago

        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?

  • 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