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