How to pass a measure as a filter to table visual in Power BI?

[monishd]

I have a calculated measure that gives me the 5th Previous Month from now (March 2024) in this format. How do I use this value to filter a table to show only data from that particular Month and Year?

Here is a demo table. As the current month is August, the 5th previous month will be March, so I would like to display data for that month. Next month, the 5th Month will change to April, and so on. 

Number	Date	Person
1	1/1/2024	xyz
2	1/2/2024	abc
3	3/2/2024	pqr
4	3/3/2024	ijk
5	4/1/2024	xyz

[xifeng_L · Accepted solution]

Hi @monishd ,

You can create a measure as below, then apply it to visual filter.

PeriodFilter = 
VAR CurRowDateMonth = FORMAT(MAX('Table'[Date]),"yyyymm")
VAR Pre5thMonth = FORMAT(EDATE(NOW(),-5),"yyyymm")
RETURN
IF(CurRowDateMonth = Pre5thMonth,1,0)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[123abc]

To dynamically filter a table visual in Power BI using a calculated measure that determines the 5th previous month, follow these steps:

1. Create a Date Table (if not already present):

   • If you don’t have a Date table, create one using DAX:

DateTable = CALENDAR(MIN('YourDataTable'[Date]), MAX('YourDataTable'[Date]))

• • Add necessary columns like Year, Month, Month-Year, etc., to this Date table.

• Create the Calculated Measure:

  • Create a measure to calculate the 5th previous month in the format Month-Year.

FifthPreviousMonth =
FORMAT(
EDATE(TODAY(), -5),
"MMMM YYYY"
)

Add a Calculated Column to Identify the Month-Year:

• In your Date table, add a calculated column to represent the Month-Year 

MonthYear = FORMAT('DateTable'[Date], "MMMM YYYY")

1. Apply the Filter Using the Measure:

   • In the Visual Level Filters of your table visual, drag the MonthYear field from the Date table and then filter it by the FifthPreviousMonth measure.
     • Set the filter condition to: MonthYear is equal to FifthPreviousMonth.

By doing this, the table visual will automatically filter to show only the records from the 5th previous month, regardless of when the report is viewed.

[monishd]

Thank you for the quick reply. I have completed all steps except for the last one, i.e., 

Apply the Filter Using the Measure:

• In the Visual Level Filters of your table visual, drag the MonthYear field from the Date table and then filter it by the FifthPreviousMonth measure.
  • Set the filter condition to: MonthYear is equal to FifthPreviousMonth.

How do I filter MonthYear = FifthPreviousMonth ?

[123abc]

To filter your MonthYear column using the FifthPreviousMonth measure in Power BI, you can follow these steps:

1. Ensure the MonthYear Column is in the Visual Filters Pane

• Add the Table Visual to your report, if not already done.
• Ensure that the MonthYear column from your date field (which should be created in step 2 of the previous instructions) is present in the Filters pane for the table visual.

2. Add the FifthPreviousMonth Measure to the Visual Filters Pane

1. Go to the Visualizations pane where your table visual is configured.
2. Find the Filters on this visual section.
3. Drag the MonthYear calculated column to the Filters pane.

3. Set the Filter Condition

1. After you have the MonthYear field in the Filters pane:
   • Click on the dropdown arrow next to MonthYear.
   • Select the Advanced filtering option.
2. In the filter options:
   • Choose "is equal to" from the dropdown.
   • Click on the small box that allows you to select a field or measure.
   • Select the FifthPreviousMonth measure.

4. Apply the Filter

• Once you've set up the condition for MonthYear is equal to FifthPreviousMonth, the filter will apply automatically.
• Your table visual should now only display the data for the month corresponding to the 5th previous month from the current date.

This setup dynamically filters your table to show only the rows from the desired month and will adjust as the current month changes.

4o