Forum Discussion

Chandrashekar's avatar
Chandrashekar
Icon for Resolver III rankResolver 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))   Reg...
  • 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
devanshi's avatar
devanshi
Icon for Helper V rankHelper V
3 years ago

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

  • Chandrashekar's avatar
    Chandrashekar
    Icon for Resolver III rankResolver 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
      Icon for Helper V rankHelper V
      3 years ago

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

      • Chandrashekar's avatar
        Chandrashekar
        Icon for Resolver III rankResolver III
        3 years ago

        Hello Devanshi,

         

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

        PBIX: Sample report 

        Regards,

        Chandrashekar B