Forum Discussion

Chandrashekar's avatar
Chandrashekar
Resolver III
3 years ago
Solved

Occurrence : Column formula help

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

  • Chandrashekar's avatar
    Chandrashekar
    3 years ago

    Hello,

     

    got the answer.

    Occurence=
    COUNTX(FILTER ( Sheet1, EARLIER ( Sheet1[Number]) = Sheet1[Number] && EARLIER(Sheet1[Month])<=[Month]),
        Sheet1[Number])
     
    Regards,
    Chandrashekar B

12 Replies

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago
        Occurences = 
var m = SELECTEDVALUE(Sheet1[Month])
var n = SELECTEDVALUE(Sheet1[Number])
return countrows(Filter(ALLSELECTED(Sheet1),Sheet1[Number]=n && Sheet1[Month]>=m))

         

  • devanshi's avatar
    devanshi
    Helper V
    3 years ago

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

    • Chandrashekar's avatar
      Chandrashekar
      Resolver III
      3 years ago

      Hello,

       

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

       

      Regards,

      Chandrashekar B

       

       

      • devanshi's avatar
        devanshi
        Helper V
        3 years ago

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