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 ,

 

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's avatar
BA_Pete
Super User
4 years ago

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