Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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~
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~
To dynamically filter a table visual in Power BI using a calculated measure that determines the 5th previous month, follow these steps:
Create a Date Table (if not already present):
DateTable = CALENDAR(MIN('YourDataTable'[Date]), MAX('YourDataTable'[Date]))
Create the Calculated Measure:
FifthPreviousMonth =
FORMAT(
EDATE(TODAY(), -5),
"MMMM YYYY"
)
Add a Calculated Column to Identify the Month-Year:
MonthYear = FORMAT('DateTable'[Date], "MMMM YYYY")
Apply the Filter Using the Measure:
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:
How do I filter MonthYear = FifthPreviousMonth ?
To filter your MonthYear column using the FifthPreviousMonth measure in Power BI, you can follow these steps:
This setup dynamically filters your table to show only the rows from the desired month and will adjust as the current month changes.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
25 | |
15 | |
14 | |
14 | |
9 |
User | Count |
---|---|
31 | |
20 | |
15 | |
15 | |
14 |