Forum Discussion
oberthju
Advocate III
Advocate IIIDAX - 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...
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
oberthjuAdvocate III
Advocate IIIHello, 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
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:
| 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!