Occurrence of Red for each customer

[Sriku]

Hi,

I want to calculate count of occurance for Red for each customer. If the new customer added it again should counting for new customer. Below is the data set:

UC ID	Date	Overall Flag	Red Flag (output)
C1	31-Jan-20	Red	1
C1	29-Feb-20	Green	0
C1	31-Mar-20	Green	0
C1	30-Apr-20	Green	0
C1	31-May-20	Red	1
C1	30-Jun-20	Green	0
C1	31-Jul-20	Green	0
C1	31-Aug-20	Green	0
C1	30-Sep-20	Green	0
C1	31-Oct-20	Red	1
C1	30-Nov-20	Red	2
C1	31-Dec-20	Red	3
C2	31-Jan-20	Red	1
C2	29-Feb-20	Red	2
C2	31-Mar-20	Red	3
C2	30-Apr-20	Green	0
C2	31-May-20	Green	0
C2	30-Jun-20	Green	0
C2	31-Jul-20	Red	1
C2	31-Aug-20	Red	2
C2	30-Sep-20	Red	3
C2	31-Oct-20	Red	4
C2	30-Nov-20	Red	5
C2	31-Dec-20	Red	6

[v-kelly-msft · Accepted solution]

Hi @Sriku ,

First create an index column;

Then create 3 columns as below:

Rank = 
var _beforeflag=CALCULATE(MAX('Table'[Overall Flag]),FILTER('Table','Table'[UC ID]=EARLIER('Table'[UC ID])&&'Table'[Index]=EARLIER('Table'[Index])-1))
Return
IF('Table'[Overall Flag]<>"Red",0,IF('Table'[Overall Flag]="Red"&&_beforeflag<>"Red",1,
RANKX(FILTER('Table','Table'[UC ID]=EARLIER('Table'[UC ID])&&'Table'[Overall Flag]="Red"),'Table'[Date],,ASC,Dense))
)

_index = 
var _previousrank=CALCULATE(MAX('Table'[Rank]),FILTER('Table','Table'[UC ID]=EARLIER('Table'[UC ID])&&'Table'[Index]=EARLIER('Table'[Index])-1))
var _diff='Table'[Rank]-_previousrank
var _index=IF(_diff>1,'Table'[Index],BLANK())
Return
_index

New Rank = 

var _index=CALCULATE(MAX('Table'[_index]),FILTER('Table','Table'[UC ID]=EARLIER('Table'[UC ID])))
Return
 IF('Table'[Index]>=_index,RANKX(FILTER('Table','Table'[UC ID]=EARLIER('Table'[UC ID])&&'Table'[Index]>=_index),'Table'[Index],,ASC,Dense)+1,'Table'[Rank])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!