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,

 

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?

    • IamTDR's avatar
      IamTDR
      Responsive Resident
      4 years ago

       

      • Knighthawk's avatar
        Knighthawk
        Helper I
        4 years ago

        Hi IamTDR,

         

        Will this work:

        let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [fiscal_year] > Date.Year(DateTime.LocalNow()) - 4)
in
    #"Filtered Rows"