Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculating Consecutive Days Worked by Staff

Hi,   I'm currently stuck trying to create a measure that will calculate the number of consecutive days worked by staff across a given period of time (date is an adjustable filter, so people can se...
Need Help
v-joesh-msft's avatar
v-joesh-msft
Icon for Solution Sage rankSolution Sage
6 years ago

Hi Anonymous ,

I am not sure if it is the result you want, you can try the measure below:

consecutive days =
VAR t =
    ADDCOLUMNS (
        ADDCOLUMNS (
            'Table',
            "LastRestDay",
            VAR currentday = [Date]
            VAR result =
                CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( 'Table', [Hours Worked] + 0 = 0 && 'Table'[Date] < currentday )
                )
            RETURN
                IF (
                    result = BLANK (),
                    CALCULATE ( MIN ( 'Table'[Date] ), 'Table' ) - 1,
                    result
                )
        ),
        "cDay", IF ( [Hours Worked] = 0, 0, DATEDIFF ( [LastRestDay], [Date], DAY ) )
    )
RETURN
    MAXX ( t, [cDay] )

Results are as follows:


Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EavYwoMQH8tLqRFXCDYz9_UB9I0dj0YDiMKOM1x8vm1o7g?e=AiT7ed

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.