Forum Discussion
Solved
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 mon...
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.
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,