Power Query M Trailing 12 Months

Question

Hello,&nbsp;&nbsp;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?&nbsp;= Table.SelectRows(dbo_CON_INCOME_STATEMENT, each ([BookName] = "Accrual") and ([sdate] &lt;= Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), -12)))&nbsp;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.&nbsp;= Table.SelectRows(dbo_CON_INCOME_STATEMENT, each ([BookName] = "Accrual") and ([sdate] = #date(2023, 1, 1) or [sdate] = #date(2023, 2, 1)&nbsp;Thank you&nbsp;

slorin · Answer

HiIf you want last 12 months then&nbsp;.... [sdate] &gt;=...&nbsp;Stéphane

anonymous · Answer

Hi&nbsp;needhelp89&nbsp;,
&nbsp;
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] &gt;= Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -12))))
This should return the last 12 months of data for the "Accrual" book.
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Power Query M Trailing 12 Months

2 Replies

Related content

12 months trailing measure

Trailing Month Slicer

Remove trailing commas

Trailing 12 months DAX

Fabric Free Trail