Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
monishd
Frequent Visitor

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

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. 

 

NumberDatePerson
11/1/2024xyz
21/2/2024

abc

33/2/2024pqr
43/3/2024ijk
54/1/2024xyz
1 ACCEPTED SOLUTION
xifeng_L
Super User
Super User

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)

 

xifeng_L_0-1723113547014.png

 

 

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

 

Thank you~

View solution in original post

4 REPLIES 4
xifeng_L
Super User
Super User

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)

 

xifeng_L_0-1723113547014.png

 

 

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

 

Thank you~

123abc
Community Champion
Community Champion

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.

 

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
Community Champion
Community Champion

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.