cancel
Showing results 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

## 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

 month Value Result 2019_08 1 3 2019_07 1 2 2019_06 1 1 2019_05 0 0 2019_04 1 2 2019_03 1 1 2019_02 0 0 2019_01 1 1

Thank you !

Olivier

1 ACCEPTED SOLUTION

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
3 REPLIES 3

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
Frequent Visitor

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:

 Date Name Status Color Points 01/01/2020 Person 1 Yes Red 0 01/01/2020 Person 1 No Red 0 01/01/2020 Person 2 Yes Red 0 01/03/2020 Person 1 Yes Blue 1 01/04/2020 Person 1 Yes Blue 1 01/07/2020 Person 2 Yes Red 0 01/072020 Person 2 Yes Blue 0 01/08/2020 Person 3 Yes Blue 0 01/10/2020 Person 2 Yes Blue 0

Thank you!

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors