Free text consequtive month filter based on another filter on the report

[rouelandrew]

Hello,

So the client wants to have a dashboard that looks like this, where the input filters include the Account name, a free text slicer where they can input a designated SLA, and another free text slicer to input number of months where a unit was below SLA.

In the example below, it's filtering only those units that were below 98.5% SLA for 3 months.

I managed to create almost everything that they are asking for except for the consequtive month free text filter since I don't know where to start. The resulting table was created using a Matrix visualization since both month and SLA's where rows in the dataset (See sample below).

Is there a way to do this or do I have to let them know that we are gonna have to fig

[v-junyant-msft · Accepted solution]

Hi @rouelandrew ,

Since you didn't provide sample data, I can only create a simple sample data myself to give you an example.
Here is my sample data:

Then I add two tables for Month Slicer and Target Slicer:

Here is the relationship view:

Then I use this DAX to create a measure:

Measure = 
VAR Today = TODAY()
VAR Select_Month = SELECTEDVALUE('Month'[Month])
VAR Target_Day = EOMONTH(TODAY(), -Select_Month) + 1
RETURN
IF(
    ISFILTERED(SLA_Target[SLA_Target]),
    IF(
        MAX('Table'[SLA]) < SELECTEDVALUE(SLA_Target[SLA_Target]) && MAX('Table'[Date]) <= TODAY() && MAX('Table'[Date]) >= Target_Day,
        1,
        0
    ),
    1
)

Set the settings according to the following screenshot:

The final output is as below:

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