Forum Discussion

IamTDR's avatar
IamTDR
Responsive Resident
3 years ago
Solved

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 fi...
Need Help
Show and Tell
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    3 years ago

    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)
ImkeF's avatar
ImkeF
Community Champion
3 years ago

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's avatar
IamTDR
Responsive Resident
3 years ago

Thanks so much!