Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
oberthju
Advocate I
Advocate I

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 Smiley Happy

 

monthValueResult
2019_0813
2019_0712
2019_0611
2019_0500
2019_0412
2019_0311
2019_0200
2019_0111

 

Thank you !

 

Olivier

1 ACCEPTED SOLUTION
oberthju
Advocate I
Advocate I

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

View solution in original post

3 REPLIES 3
oberthju
Advocate I
Advocate I

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

Hello,

I feel like I am trying to figure out a similar problem, but the proposed solution in the thread above does not quite work form me. Could anyone help me to figure this out, please?

 

I need to figure out how to assign points to anyone who met ALL of the following rules:

1) status "Yes",

2) in a single color category,

3) at least 2 consecutive times.

 

In the example below, that would be Person 1, on Jan 3 and Jan 4th, color Blue.

 

Here is my data:

DateNameStatusColorPoints
01/01/2020Person 1YesRed0
01/01/2020Person 1NoRed0
01/01/2020Person 2YesRed0
01/03/2020Person 1YesBlue1
01/04/2020Person 1YesBlue1
01/07/2020Person 2YesRed0
01/072020Person 2YesBlue0
01/08/2020Person 3YesBlue0
01/10/2020Person 2YesBlue0

 

Thank you!

TomMartens
Super User
Super User

Hey,

 

as indexing sequences is not as easy as it should 🙂 please have a look at this thread and try to adapt my answer(s) to your data: https://community.powerbi.com/t5/Desktop/Measure-to-calculate-count-of-accounts-which-has-2-or-more/...

 

If you need more help, please create a pbix file that contains sample data, upload the file to onedrive or dropbox and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors