Occurrence : Column formula help

[Chandrashekar]

Hello Team,

Can you help me to get column formula for occurrence as mentioned in attachment.

PBIX: Sample file

Excel Formula: =MAX(IF(B2:B6=B2,ROW(B2:B6)-ROW(INDEX(B2:B6,1,1))+1))

Regards,

Chandrashekar B

[devanshi]

Occurences = COUNTROWS([Number], FILTER( 'Tablename', [Month] = EARLIER[Month]))
OR
Occurences = COUNTROWS( FILTER( 'Tablename', [Month] = EARLIER[Month]))

[Chandrashekar]

Hello,

am getting wrong count and first one is not working. I should get same result as [Expected output].

Regards,

Chandrashekar B

[devanshi]

TRY this, 
Occurences = COUNTROWS([Number],ALLEXCEPTED( 'Tablename', [Month] = EARLIER[Month]))

[Chandrashekar]

Hello Devanshi,

am getting error while using column formula. Please find sample data.

PBIX: Sample report 

Regards,

Chandrashekar B

[lbendlin]

access to sample is denied

[Chandrashekar]

Hello,

Please find updated link for sample report.

PBIX: Sample report 

Regards,

Chandrashekar B

[lbendlin]

Occurences = 
var m = SELECTEDVALUE(Sheet1[Month])
var n = SELECTEDVALUE(Sheet1[Number])
return countrows(Filter(ALLSELECTED(Sheet1),Sheet1[Number]=n && Sheet1[Month]>=m))

[Chandrashekar]

Hello, Can I get column formula instead of DAX.

Regards,

Chandrashekar B

[lbendlin]

Occurences = countrows(Filter(ALL(Sheet1),Sheet1[Number]=[Number] && Sheet1[Month]>=[Month]))

[Chandrashekar]

Hello,

am getting same numbers.

Regards,

Chandrashekar B

[Chandrashekar]

Hello,

Can anyone help on column formula.

Regards,

Chandrashekar B

[Chandrashekar · Accepted solution]

Hello,

got the answer.

Occurence=

COUNTX(FILTER ( Sheet1, EARLIER ( Sheet1[Number]) = Sheet1[Number] && EARLIER(Sheet1[Month])<=[Month]),

    Sheet1[Number])

Regards,

Chandrashekar B