Filter data for Last 12 Months using Power Query

[hamzashafiq]

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. 

[rohit_singh]

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! 🙂

[ribisht17 · Accepted solution]

@hamzashafiq 

Please check Solved: How to limit power query to last 12 months of data... - Microsoft Power BI Community

Thanks,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users