Power Query M Trailing 12 Months

[needhelp89]

Hello, 

I am fairly new to Power Query and I am trying to pull in the last 12 months of data. Here is what I have but even if I do one month instead of 12 for testing it runs and runs millions of rows when it shouldn't be that much. Any idea what i am doing wrong?

= Table.SelectRows(dbo_CON_INCOME_STATEMENT, each ([BookName] = "Accrual") and ([sdate] <= Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), -12)))

I am able to pull in specific months, see below, and it works fine. Just want to change it to last 12 months so I don't have to manually update in the future.

= Table.SelectRows(dbo_CON_INCOME_STATEMENT, each ([BookName] = "Accrual") and ([sdate] = #date(2023, 1, 1) or [sdate] = #date(2023, 2, 1)

Thank you 

[Anonymous]

Hi @needhelp89 ,

One way to optimize the query is to use the "StartOfMonth" function to get the start of the month for the current date, and then subtract 12 months to get the start of the month for the date 12 months ago. You can then use this value to filter the table. Here is an example of how you can modify your query:

= Table.SelectRows(dbo_CON_INCOME_STATEMENT, each ([BookName] = "Accrual") and ([sdate] >= Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -12))))

This should return the last 12 months of data for the "Accrual" book.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[slorin]

Hi

If you want last 12 months then 

.... [sdate] >=...

Stéphane