Create Filter on Column for visual showing 5 month window including last, current, and 3 next months

[patkelly1008]

I have reports with a data set from Microsoft Project Web App. One of these reports has 3 visuals of Resource Availability. I'm trying to create a filter which allows me to show last month's availability, current month, and the next 3 months. Similiar to if you created a built in relative date filter that could overlap using an 'Or'. I've been accomplishing this monthly by going in and swapping checkboxes, but I'm trying to rid myself of that task and just can't solve the expression to do it.

I'm currently tapped into the Tables 'Resource Name' and 'TimeSet' to show the data. 

What I think would work is if I could find the way the native filters are expressed and throw them into an 'Or' statement but have been unable to accomplish that utilizing TODAY().

The visuals I'm referring to with details redacted.

[v-xinruzhu-msft]

Hi @patkelly1008 

You can create a date table first, then add a column to judge if it is the current month or other types, pleaserefer to the following links:

Slicer to get Current Year/Pervious Year and Curre... - Microsoft Power BI Community

Power BI: Creating a Date Table using DAX (linkedin.com)

Use a relative date slicer or filter in Power BI - Power BI | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[patkelly1008 · Accepted solution]

So the slicers are what I had already tried and I couldn't add slicers that went both backward and forward. I ended up figuring out a resut and wrote the following, which worked after drawing the right model relationships:

Calendar =
ADDCOLUMNS(
            CALENDAR(
                    Date(2022,1,1),
                    Date(2022,12,31)
                    ),
            "YYYY-MM", 
                      Format([Date],"YYYY-MM"),
            "Month_filter",
                        VAR CurrentDate = TODAY()
                        VAR ThisMonth = Format(EOMONTH(CurrentDate,
                                               0),"YYYY-MM")
                        VAR LastMonth = Format(EOMONTH(CurrentDate, 
                                               -1),"YYYY-MM")
                        VAR NextMonth1 = Format(EOMONTH(CurrentDate, 
                                               1),"YYYY-MM")
                        VAR NextMonth2 = Format(EOMONTH(CurrentDate, 
                                               2),"YYYY-MM")
                        VAR NextMonth3 = Format(EOMONTH(CurrentDate, 
                                               3),"YYYY-MM")
                        VAR month_list = COMBINEVALUES(",",
                                                        ThisMonth,
                                                        LastMonth,
                                                        NextMonth1,
                                                        NextMonth2,
                                                        NextMonth3
                                                      )
                                                      
                        Return
                        if(
                            CONTAINSSTRING(month_list,
                                           Format([Date],"YYYY-MM")),
                            TRUE(),
                            FALSE())
            )