Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamically how to filter current fiscal year through M code in Power query| Power BI

We have a table which consists of calender dates until 2030 in Month/Date/Year format. Our requirement is to dynamically filter this date column to current fiscal year. Our Fiscal year start from Jun...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    According to your description:

    For example if we are in July 2022 then then it should show us all the dates from June 2022 to May 2023 as per our fiscal years which starts from june of every year.

     

    • if current month <=6 then should keep dates from previous/6/1-- current/5/31
    • if current month >6 then should keep dates from current/6/1 -- next/5/31

     

    If so , as Anonymous mentioned, please create new blank queries to get the "Start Date" and "End Date". Below is my method.

     

    let
    Source = DateTime.Date(DateTime.LocalNow()),
    currentmonth= Date.Month(Source),
    startYear= if currentmonth<=6 then Date.Year(Source)-1 else Date.Year(Source),
    startDate=Date.From(Text.From(startYear) & " 6 01")
in
    startDate

     

    let
    Source = DateTime.Date(DateTime.LocalNow()),
    currentmonth= Date.Month(Source),
    endYear=if currentmonth<=6 then Date.Year(Source) else Date.Year(Source)+1,
    endDate=Date.FromText(  Text.From(endYear) &" 5 31")
in
    endDate

     

    Then use Table.SelectRows() to filter dates:

    #"Filtered Rows"= Table.SelectRows(#"Changed Type", each [Date] >= #"Start Date" and [Date] <= #"End Date" )
in
#"Filtered Rows"

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi Anonymous ,

 

The way that I do this is by first adding a fiscal year column:

 

addFinYear =
Table.AddColumn(
    previousStep,
    "finYear",
    each Date.Year([date]+#duration(275,0,0,0))
)

 

 

The duration added here is for an April 1st fiscal year start. To start on 1st June, I think you would update this to 214, but make sure to test.

 

Once you have that column, you can add a relative fiscal year column:

 

addRelativeFY =
Table.AddColumn(
    addFinYear,
    "relativeFY",
    each [finYear] - Date.Year(Date.From(DateTime.LocalNow())+#duration(275,0,0,0))
)

 

 

Again, you will need to change the duration to meet with your fiscal year start.

 

Now all you need to do, in either Power Query or DAX, is to filter on [relativeFY] = 0 to get current FY, [relativeFY] = -1 for prior year etc.

 

Pete