Filter rows

[Sipra1]

Basically I have a table which has data according to financial years which is from September to August. So for example calendar year 2025 September will be Financial year 2026 september.

I have many years in my table 2023,2024,2025,2026.
In 2026 I already have data for months Sep,Oct,Nov,Dec,Jan(We will not include current month data in visualization)

So My question is - when I am trying to filter out jan month from Financial year 2026, I am unable to do it. For example if I chose "is before" 1-jan-2026(See screenshot) it is not displaying Financial year 2026 sep,oct,nov,dec months data as well, so how do I filter so that only the current month data is excluded in this case Jan 2026 but should display other months in 2026(Finanacial year)?

[V-yubandi-msft]

Hi @Sipra1 ,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @ronrsnfld , @ralf_anton , for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member's, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[ralf_anton]

Hi,

nur den Januar des aktuellen Jahres filtern:

= Table.SelectRows(Source, each Date.Month([Dates]) = 1 and Date.IsInCurrentYear([Dates]))

[ronrsnfld]

To filter out the current month, you need to use the Advanced Editor with a line like:

#"Remove Current Month" = Table.SelectRows(#"Previous Step", each [Dates] < Date.StartOfMonth(Date.From(DateTime.LocalNow())))

Replace #"Previous Step" with the obvious.

You could also add a Choose Rows step from the UI, and, in the formula bar, edit what you see to show the above selection criteria.

Here is an example, including creating a column of dates spanning several years, that will return only the rows with months in the current fiscal year excepting the current month:

let
    fyStart=[a=Date.From(DateTime.FixedLocalNow()),
             b=Date.Month(a),
             c=Date.Year(a),
             d=if b < 9 then #date(c-1,9,1) else #date(c,9,1)][d],

//Create table with date column over several fiscal years
    Source = #table(type table[Date=date],List.Accumulate({1..36},{}, 
                    (s,c)=>s & {{Date.AddMonths(#date(2023,1,1),c)}})),

//Select current fiscal year except current month
    #"Current FY" = Table.SelectRows(Source, each [Date]>=fyStart and [Date] < Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())))
   
in
    #"Current FY"

Source Table:

Results: