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"

     

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

        No.  It wont because the field 'fiscal_year' is seen as a number and not a date.
        I usually duplicate this table, although I do not enable load, remove other fields except date field, remove duplicates, sort, and then keep first 4 rows.  Then I do a InnerJoin between the two tables.  This keeps the data being native but the results are rather slow.  Before doing this I would manually filter the table but I no longer want to revise this report yearly.  Goal is to be dynamic.