Results 1 to 6 of 6

Thread: Trailing 12 Months (TTM)

  1. #1
    Seeker Kirk P.'s Avatar
    Join Date
    Nov 2016
    Location
    Minnesota USA
    Posts
    10
    Articles
    0
    Excel Version
    365

    Trailing 12 Months (TTM)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Acolyte Kaso's Avatar
    Join Date
    Apr 2017
    Posts
    24
    Articles
    0
    Excel Version
    2013, 2016, 365
    Hey, could you please upload a sample that i could work with?

  3. #3
    Seeker Kirk P.'s Avatar
    Join Date
    Nov 2016
    Location
    Minnesota USA
    Posts
    10
    Articles
    0
    Excel Version
    365
    Sample file attached....
    Attached Files Attached Files

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    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:
    Code:
    mystartdate = DateTime.Date(Date.AddYears(fnGetDateParameter("End Date"),-1)),
    to:
    Code:
    mystartdate = Date.AddYears(myenddate,-1),
    seems to sort it.

  5. #5
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Ok, but why didn't the immediately preceding statement

    Code:
    myenddate = DateTime.Date(fnGetDateParameter("End Date")),
    throw an error?

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by NormS View Post
    Ok, but why didn't the immediately preceding statement
    Code:
    myenddate = DateTime.Date(fnGetDateParameter("End Date")),
    throw an error?
    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:
    Code:
    FilteredRowsTTM = Table.SelectRows(qryDateRange_All, each [PeriodDate] >= mystartdate and [PeriodDate] < myenddate)
    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.com...rum=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/0...a-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.com...rum=powerquery
    but I haven't read it properly yet.
    Last edited by p45cal; 2020-05-31 at 12:53 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •