Forum Discussion

oberthju's avatar
oberthju
Icon for Advocate III rankAdvocate III
7 years ago
Solved

DAX - how to count consecutive identical values ?

Hi,    I would need a DAX formula to calculate the Result column which is the total number of consecutive 1 in Value columns, starting from current month descending.  I'm sure you know :smileyhappy...
Need Help
  • oberthju's avatar
    oberthju
    7 years ago

    Hello, thanks for your replies, I finally found my way of doing it.

    Hope this helps.

     

    nb consecutive months to date =
    VAR month =
        CALCULATE (
            MAX ( Table1[mois] );
            FILTER (
                CALCULATETABLE ( Table1; ALL ( Table1 ); Table1[valeur] = 0 );
                Table1[mois] <= EARLIER ( Table1[mois] )
            )
        )
    RETURN
        CALCULATE (
            COUNTROWS ( Table1 );
            FILTER (
                Table1;
                Table1[mois] <= EARLIER ( Table1[mois] )
                    && Table1[mois] > month
            )
        ) + 0