March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
25 | |
18 | |
15 | |
9 | |
8 |
User | Count |
---|---|
37 | |
32 | |
21 | |
16 | |
14 |