Power Query: Filter a Date Field by Current and Prior Fiscal Year (Keeping Query Native)

[IamTDR]

Hi

I have a massive table that has a single Date field.  I am interested in applying a step in power query to filter this table by the current fiscal year and the prior fiscal year.  My company's fiscal year begins on May 1 and ends April 30th.  Ideally I'm hoping this step would be able to keep the query native to maintain performance.
Is this possible?

[ImkeF · Accepted solution]

Hi @IamTDR ,
you are absolutely correct - sorry, didn't think about that.

You can adjust like so:

if Date.Month(Date.From(DateTime.LocalNow())) < 5 then #date(Date.Year(Date.From(DateTime.LocalNow())) - 2, 5, 1) else #date(Date.Year(Date.From(DateTime.LocalNow())) - 1, 5, 1)

and

if Date.Month(Date.From(DateTime.LocalNow())) < 5 then #date(Date.Year(Date.From(DateTime.LocalNow())) , 4, 30) else #date(Date.Year(Date.From(DateTime.LocalNow())) + 1, 4, 30)

[IamTDR]

Thanks so much!

[ImkeF]

Hi @IamTDR ,
the dynamic start parameter can be written like so: 

#date(Date.Year(Date.From(DateTime.LocalNow())) - 1,5,1)

and the end-parameter like so:

#date(Date.Year(Date.From(DateTime.LocalNow())) + 1,4,30)

Just filter your table using the UI by dummy-values and replace the fixed dates by the expressions above.
Query folding should still work.

[IamTDR]

Thank you. I was able to get this to work and maintain query folding.
Will this still work say come January 2023??  If my fiscal year is May - April, come this Jan23, wouldnt this make my results be 5/1/22 - 4/30/24 ???  In which case that would not be correct. May 2022 through April 2023 results should be 5/1/21 - 4/30/23