Dynamic filtering of a page

[Scubadiver007]

Hi,

I have a table for the school census of which one column is the census year. How do I dynamically filter a page to only show info for the current year?

I've thought I would need to add an IF variable of some kind to return the most recent year and had a search but can't find a solution.

TIA,

David

[danextian · Accepted solution]

Hi @Scubadiver007 

Create a calculated column that checks whether the year in the current row is the max year in the table. The calculated column below will return true or false. Use this as a page level filter.

IsLatestYear = 
MAX ( Dates[Calendar Year] ) = Dates[Calendar Year]

[V-yubandi-msft]

Hi @Scubadiver007 ,

I hope my message clarified everything. If everything is resolved, please mark it as Accepted solution. Please let me know if you are facing any issues.

Thank You.

[v-hjannapu]

Hi @Scubadiver007,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you

[V-yubandi-msft]

Hi @Scubadiver007 ,

Could you confirm whether the solution provided resolved your issue or if you are still experiencing problems.
Your feedback will help us enhance the quality of our responses for everyone.

If your issue has been resolved, please consider accepting the solution to assist others in finding the correct answer more easily.

Thank You.

[danextian · Accepted solution]

Hi @Scubadiver007 

Create a calculated column that checks whether the year in the current row is the max year in the table. The calculated column below will return true or false. Use this as a page level filter.

IsLatestYear = 
MAX ( Dates[Calendar Year] ) = Dates[Calendar Year]

[mdaatifraza5556]

Hi @Scubadiver007 

Create cal col using below dax.

IsCurrentYear =
YEAR(TODAY()) = 'Date'[Year]

Then:
        Use  IsCurrentYear = TRUE() in Page level Filter.

If this answer your questions, kindly accept it as a solution and give kudos.

[Scubadiver007]

Thanks for this. A lot simpler than I thought, but I would like a modification if possible.

The census isn't finalised until March. How would I modify this formula to return information for the previous year if the date is from 1st January to 31st March?

[mdaatifraza5556]

Hi @Scubadiver007 

Can you please try this as per your requirement.

IsCurrentCensusYear =
VAR TodayDate = TODAY()
VAR ThisYear = YEAR(TodayDate)
VAR EffectiveYear =
IF(
MONTH(TodayDate) <= 3,
ThisYear - 1,
ThisYear
)
RETURN
'Date'[Year] = EffectiveYear



If this answers your questions, kindly accept it as solution and give kudos.

[V-yubandi-msft · Accepted solution]

Hi @Scubadiver007 ,

Thank you for reaching out to the Microsoft Fabric Community.  It works perfectly I tested it with sample data, and the logic correctly returns the previous year from January to March, and the current year from April onwards  exactly as required for the census scenario.

Make sure to use the IsCurrentCensusYear column in the page level filter and set it to TRUE to dynamically display the correct census year.  To help others understand better, I've attached a sample .pbix file for reference.

FYI:

Thanks for the help full response @mdaatifraza5556 .

If you find this response helpful, kindly mark it as the accepted solution  it may help others find the answer quickly.

[bhanu_gautam]

@Scubadiver007 , Open your Power BI Desktop.
Go to the "Modeling" tab and click on "New Column".
Enter the following DAX formula to create a column that holds the current year:

CurrentYear = YEAR(TODAY())

Go to the "Data" view and select your table that contains the census data.
Ensure that your table has a column for the census year (e.g., CensusYear).
Go to the "Report" view and select the visual or page you want to filter.
Drag the CensusYear field to the Filters pane.
Set the filter condition to show only the rows where CensusYear equals the CurrentYear column you created.

If you want the filter to apply to the entire page, you can set it as a page-level filter.
In the Filters pane, drag the CensusYear field to the "Page level filters" section.
Set the filter condition to CensusYear equals CurrentYear.