Forum Discussion
Power Query: Filter a Date Field by Current and Prior Fiscal Year (Keeping Query Native)
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?
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)
4 Replies
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.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
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)Thanks so much!
