Consecutive Zero Values

[readyraddy]

Hi all,

I have a table of data as shown below. I would like to count the consecutive number of days(5days) where the value of 'Operation' is Zero. The consecutive days should be unique to the 'Location' & 'Status2'.

Desired Output:

I filtered the data to better visualise the desired output. In this case, an additional column has been added to count the consecutive days where the value of 'Operation' is zero. 

Hope to get some help here. Thanks!

[v-frfei-msft · Accepted solution]

Hi @readyraddy ,

Here I have created a sample for your reference. 

Measure = 
VAR a =
    MAX ( 'Table'[DateTime] ) - 5
VAR b =
    MAX ( 'Table'[DateTime] )
VAR cov =
    CALCULATE (
        SUM ( 'Table'[Operation] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Location], 'Table'[Status2] ),
            'Table'[DateTime] > a
                && 'Table'[DateTime] <= b
        )
    )
RETURN
    IF ( cov = 0, 1, BLANK () )

Measure 2 = 
CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Measure] = 1 ) )

For more details, please check the pbix as attached.If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.