Forum Discussion
Count consecutive days between months
Hi Im pretty new to Power BI and I can't think how to do a measure to calculate this.
I want to calculate if someone has assist 3 to 5 days in a row, an if its true, count 1 asisstance in the month of the latest day.
I have this table:
| Person | day | Assist |
| A | 28/01/22 | 1 |
| A | 29/01/22 | 1 |
| A | 30/01/22 | 1 |
| A | 31/01/22 | 1 |
| A | 01/02/22 | 1 |
| B | 02/02/22 | 1 |
| B | 03/02/22 | 0 |
| B | 04/02/22 | 1 |
| C | 05/02/22 | 1 |
| C | 06/02/22 | 1 |
| C | 07/02/22 | 1 |
I want a result table like this:
| Person | Month | Assistance |
| A | February | 1 |
| C | February | 1 |
Any help would be very appreciate.
Many thanks.
Hi xrieraca ,
You can create two measures to get it.
Assistance = VAR _sum = CALCULATE ( SUM ( 'Table'[Assist] ), FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) ) ) VAR _count = CALCULATE ( COUNT ( 'Table'[Assist] ), FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) ) ) RETURN IF ( _count = _sum, 1 )Month = FORMAT ( MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) ), [day] ), "mmmm" )Note:set the Assistance measure show items when the value is 1.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
xrieraca You could use a repeating counter like Cthulhu. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211
Hi xrieraca ,
You can create two measures to get it.
Assistance = VAR _sum = CALCULATE ( SUM ( 'Table'[Assist] ), FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) ) ) VAR _count = CALCULATE ( COUNT ( 'Table'[Assist] ), FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) ) ) RETURN IF ( _count = _sum, 1 )Month = FORMAT ( MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Person] = MAX ( 'Table'[Person] ) ), [day] ), "mmmm" )Note:set the Assistance measure show items when the value is 1.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It worked!
Many thanks,
Perhaps these two measures:
Assistance = VAR MyTable = ADDCOLUMNS( ADDCOLUMNS( 'Table', "Concat", CONCATENATEX( FILTER( 'Table', 'Table'[Person] = EARLIER( 'Table'[Person] ) && 'Table'[day] <= EARLIER( 'Table'[day] ) ), 'Table'[Assist], , 'Table'[day], ASC ) ), "Check", 0 + ( VALUE( RIGHT( [Concat], 3 ) ) = 111 ) ) RETURN 0 + ( SUMX( MyTable, 0 + ( [Check] > 0 ) ) > 0 )Month = VAR MyTable = ADDCOLUMNS( ADDCOLUMNS( 'Table', "Concat", CONCATENATEX( FILTER( 'Table', 'Table'[Person] = EARLIER( 'Table'[Person] ) && 'Table'[day] <= EARLIER( 'Table'[day] ) ), 'Table'[Assist], , 'Table'[day], ASC ) ), "Check", 0 + ( VALUE( RIGHT( [Concat], 3 ) ) = 111 ) ) RETURN FORMAT( MAXX( MyTable, IF( [Check] > 0, 'Table'[day] ) ), "mmmm" )which can then be placed into, for example, a simple Table visual alongside the Person field.
Regards
