M Query: How to filter data to start of fiscal year, five years ago?

[Shelley]

Hi All,

I use the below all the time to limit data coming into Power BI via the Query editor. For example, this limits the data to five years back from today, based on the create_dttm field.

    #"Filtered Rows" = Table.SelectRows(srvc_tckt_View, each Date.From([create_dttm]) >= (Date.AddYears(Date.From(DateTime.LocalNow()),-5))),

The problem is, this always provides a partial fiscal year at the beginning of the dataset. I want my users to have five FULL years of data, plus the current fiscal year to date. So, how do I do the above, but go back to October 1 of that fiscal year?

Examples:
If Today =            Result I want

Feb 2, 2020          Oct 1, 2014

Oct 31, 2019        Oct 1, 2014

May 30, 2021       Oct 1, 2015

Oct 25, 2020        Oct 1, 2015

Is this even possible? Or is it a nightmare?

[parry2k · Accepted solution]

@Shelley  try this

  #"Filtered Rows" = Table.SelectRows(srvc_tckt_View, each Date.From([create_dttm]) >= (Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-5)))),

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡