How to display clients which have had 3 consecutive negative months in values

[av9]

I am trying to flag which clients have had the last 3 consecutive months as negative values. My data looks like the table below:

Fields: Client, Date,  Value

Client	Feb-20	Mar-20	Apr-20
Client A	-$100.00	-$200.00	-$ 500.00
Client B	$500.00	$120.00	-$ 800.00
Client C	$1,150.00	$500.00	$ 900.00

The output would be showing me Client A as this meets the criteria.

Client	Feb-20	Mar-20	Apr-20
Client A	-$100.00	-$200.00	-$ 500.00

[v-yiruan-msft]

Hi @av9 ，

You can create a measure as below:

Client with 3 consecutive negative months = 
CALCULATE (
    MAX ( 'Client'[Client] ),
    FILTER (
        'Client',
        'Client'[20-Feb] < 0
            && 'Client'[20-Mar] < 0
            && 'Client'[20-Apr] < 0
    )
)

Best Regards

Rena

[av9]

I have dates setup in a calender table, so it doesn't work in this way

[amitchandak]

@av9 , try

consecutive negative =
Var _tab =summarize(filter(Table,DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)),Table[Client],Table[Month],"_1", sum(Table[Value])
return
countX(filter(summarize(_tab,[Client],"_1",if(sum(Table[Value])>0,1,0) ),[_1] >=3),[Client])

[av9]

I can't quite get the measure to work, I should explain the data model is set up in 3 separate tables:

Client Table(dim), Date Table(dim),  Value Table(fact)

[v-yiruan-msft]

Hi @av9 ,

In order to provide you a suitable measure or calculated column, could you please provide some sample data(exclude sensitive data) in these three tables and existed relationships among them? It is better if you can provide your report file directly. Thank you.

Best Regards

Rena