Trailing 12 Months (TTM)

I thought this was a fairly straightforward date filter, but I'm getting this error: (step 'FilteredRowsTTM') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

let
myenddate = DateTime.Date(fnGetDateParameter("End Date")),
mystartdate = DateTime.Date(Date.AddYears(fnGetDateParameter("End Date"),-1)),
Source = qryDateRange_All,
FilteredRowsTTM = Table.SelectRows(Source, each [PeriodDate] >= mystartdate and [PeriodDate] < myenddate)
in
FilteredRowsTTM

The user supplies their desired "end date" as a parameter, and it counts back 1 year to retrieve a rolling 12 months. Help!

Hey, could you please upload a sample that i could work with?

Sample file attached....

I know it's a 3 year old thread - I'm using these old threads to practise on.
The error was a 'firewall' one.
Changing:
to:
seems to sort it.

Ok, but why didn't the immediately preceding statement


throw an error?

NormS said:

Ok, but why didn't the immediately preceding statement

Code:

myenddate = DateTime.Date(fnGetDateParameter("End Date")),

throw an error?

Click to expand...

Well, I didn't know the answer to that one; and I still don't!
On my machine the error wasn't thrown by either of those two fnGetDateparameter lines but by the last line with the error message::
Formula.Firewall: Query 'qryDateRange_TTM' (step 'FilteredRowsTTM') references other queries or steps, so it may not directly access a data source
which is:
According to Ken Puls: "you have connected to two completely different data sets using the same query. It doesn’t matter if you are working with two tables from the same workbook; they are seen as two data sets, and Power Query doesn’t like this"
but this doesn't explain why my suggestion works! There are still two datasets.

There's more in-depth stuff here form the team giving us Power Query:
https://social.technet.microsoft.co...of-the-data-privacy-firewall?forum=powerquery
but I haven't read it properly yet.

There's more from Ken Puls here on this problem:

Power Query Errors: Please Rebuild This Data CombinationThe Excelguru Blog
https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

but, to my mind, this still doesn't explain why my suggestion works.

So if you come across an explanation I'd be glad to know it!

There's more in-depth stuff from the team who give us Power Query here:
https://social.technet.microsoft.co...of-the-data-privacy-firewall?forum=powerquery
but I haven't read it properly yet.