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 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.



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

     

3 Replies

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I dont think this will be dynamic - can anyone advice?