Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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 availabi...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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())
            )

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous 

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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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())
            )