Forum Discussion
Last 3 Months Filter
I have a Date Table and would like to Filter the Last 3 Months. This means if we are in the Month of December, there should be November, October and September all crumbled together as one item on a filter. How is this possible? Thanks.
Hi Anonymous,
You can create an unconnected date table and use it as source slicer. Then you can write a measure formula to compare selected date and current date if it is including in the three months and use it on your visual to filter records.
Applying a measure filter in Power BI - SQLBI
formula = VAR currDate = MAX ( Table[Date] ) VAR selected = MAX ( NewTable[Date] ) RETURN IF ( currDate >= DATE ( YEAR ( selected ), MONTH ( selected ) - 3, DAY ( selected ) ) && currDate <= selected, "Y", "N" )Regards,
Xiaoxin Sheng
2 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure how your datamodel looks like, but one of ways to solve this is using DATESINPERIOD DAX function.
For the StartDate parameter in the function, try using EOMONTH( TODAY(), -1) which gives end date of previous month.
I hope this helps.
Hi Anonymous,
You can create an unconnected date table and use it as source slicer. Then you can write a measure formula to compare selected date and current date if it is including in the three months and use it on your visual to filter records.
Applying a measure filter in Power BI - SQLBI
formula = VAR currDate = MAX ( Table[Date] ) VAR selected = MAX ( NewTable[Date] ) RETURN IF ( currDate >= DATE ( YEAR ( selected ), MONTH ( selected ) - 3, DAY ( selected ) ) && currDate <= selected, "Y", "N" )Regards,
Xiaoxin Sheng
