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

[Anonymous]

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 June to May which is June/2021 to May/2022 which is FY22. We have  converted the calander date to fiscal year and fiscal month but unable to filter dynamically  with current fiscal year in power query. We tried writing multiple If conditions to get the current fiscal year through M which is not working.

Can you please help me out to implement this logic through M query.

 

[Anonymous · Accepted solution]

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.

[Anonymous]

I would do this:

First make a new blank query to calculate the FY for today. Name the queryFiscalYr:

= "FY "& Text.From(Year.From(Date.AddMonths(Date.From(DateTime.LocalNow()), 6)))

On your calendar or fact table, you can add this column:

=Table.AddColumn(TableOrPriorStepName, "FY", each "FY "& Text.From(Year.From(Date.AddMonths([Date], 6))))

Now you can filter like:

= Table.SelectRows(PriorStepOrTableName, each [FY] = FiscalYr)

--Nate

Then you can add a filter, 

[Anonymous]

Thankshttps://community.powerbi.com/t5/user/viewprofilepage/user-id/226208, https://community.powerbi.com/t5/user/viewprofilepage/user-id/115827
Thanks for the solution but i did convert my dates to fiscal year and fiscal month the problem is dynamically every fiscal year it should automatically filter and show us only current Fiscal year dates. 
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.
Dynamically how should i achieve this in power query

[BA_Pete]

Hi @Anonymous ,

This is exactly what I gave you.

Create the [relativeFY] column, then filter on [relativeFY] = 0, then it will always filter for CURRENT fiscal year dynamically.

Can you describe which part of it isn't working for you and we'll get it sorted.

Pete

[BA_Pete]

Hi @Anonymous ,

Paste the following code into a new blank query to see how the [relativeFY] column/filter works.

No extra queries, no calculations referencing other queries, just one step to make one column that you can filter on:

let
  // Define Date.Today
  Date.Today = Date.From(DateTime.LocalNow()),
  Source = {Number.From(#date(Date.Year(Date.AddYears(Date.Today + #duration(214,0,0,0),-3)),6,1))..Number.From(#date(Date.Year(Date.AddYears(Date.Today + #duration(214,0,0,0),30)),5,31))},
  convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
  renCols = Table.RenameColumns(chgDateType, {{"Column1", "date"}}),
  addFinYear = Table.AddColumn(renCols, "finYear", each Date.Year([date] + #duration(214,0,0,0))),

  addRelativeFY = Table.AddColumn(addFinYear, "relativeFY", each [finYear] - Date.Year(Date.Today + #duration(214,0,0,0))),

  chgTypes = Table.TransformColumnTypes(addRelativeFY,{{"finYear", Int64.Type}, {"relativeFY", Int64.Type}}),
  filterRelativeFY0 = Table.SelectRows(chgTypes, each ([relativeFY] = 0))
in
  filterRelativeFY0

You can see from the addRelativeFY step that it is using a reference to 'Today's' date to create the relative value. This is what ensures it is dynamic.

Pete

[BA_Pete]

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

[mahoneypat]

Please try a custom column with the expression below, which will generate a FY column based on June to May. Once you have that, you can filter to FY2022 (or keep past FYs too).

= "FY" & Number.ToText(if Date.Month([Date])<= 5 then Date.Year([Date]) else Date.Year([Date]) + 1)

Pat