Forum Discussion

hamzashafiq's avatar
hamzashafiq
Kudo Collector
4 years ago
Solved

Filter data for Last 12 Months using Power Query

Dear All,

 

I have a table, I want to filter it for Last 12 Months on the data column which I have in the table. For example the max date I have in the data is 25/05/2022, the filter data should be between 26/05/2022 and 25/05/2022. I can do this using DAX but I want to get this done on Power Query before loading the data in report. 

Need Help

2 Replies

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi hamzashafiq ,

    I assume there is a typo and your date range is 26/05/2021 to 25/05/2022. 

    Consider my date table that has max date = today's date as shown below

     

    2) Open a new blank query and create a parameter for max date

     

    = List.Max(dim_date[Date])

     

    3) Similarly, create a parameter for min date

    = Date.AddMonths(List.Max(dim_date[Date]), -12)

     

    4) Filter your data table using these parameters 

     

    5) Choose any random dates since we will be replacing these with parameters that we created above


    6) Replace the dates with the parameters and your data will be filtered as per the query

     

    Please do keep in mind that you cannot create parameters from and use it to filter the same table, since you will get a cyclic reference error. 


    I've created the parameters using my date table and filtered a copy of the date table using it.

    Hence, I would suggest choosing the date column and adding it as a new query to compute your min date parameter

     

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂