Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |