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 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.

 

 

 

 

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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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'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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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,