Please Help! For each Serial Number i need to count the days its consecutively Active

[LynnG]

Using PowerBi Live Connection - I have a serial number field and 1 date field.

I need a measure that calculates the number of days a unit (Serial Number) is Active for a concecutive number of days. I am using a Live Connection so I cant just do it in a local desktop table. So for the first serial number on 1/1 it was Active for 1 day and counts up until a day is missed, then starts over again for each change in serial number.

So for Each serial number, sort by date to calculate how many days it has been consecutively active and then re-start the count. (If it is listed then it is Active.)

Serial Number	summary_date	consecutive days
ABXM6U16736	1/1/2023	1
ABXM6U16736	1/2/2023	2
ABXM6U16736	1/3/2023	3
ABXM6U16736	1/4/2023	4
ABXM6U16736	1/6/2023	1
ABXM6U16736	1/7/2023	2
ABXM6U16736	1/8/2023	3
ABXM6U16736	1/9/2023	4
ABXM6U16736	1/10/2023	5
ABXM6U16736	1/11/2023	6
ABXM6U16736	1/12/2023	7
ABXM6U16686	1/1/2023	1
ABXM6U16686	1/2/2023	2
ABXM6U16686	1/3/2023	3
ABXM6U16686	1/7/2023	1
ABXM6U16686	1/8/2023	2
ABXM6U16686	1/10/2023	1
ABXM6U16686	1/11/2023	2
ABXM6U16686	1/12/2023	3

[Anonymous · Accepted solution]

Hi @LynnG ,

I have created a simple sample, please refer to it to see if it helps you.

Create measures.

flag =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[summary_date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Serial Number] ),
            [summary_date] < MAX ( 'Table'[summary_date] )
        )
    )
VAR _b =
    MAX ( 'Table'[summary_date] ) - 1
RETURN
    IF ( _a <> _b, 0, 1 )

minflagdate = 
CALCULATE (
    MAX ( 'Table'[summary_date] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[summary_date] <= SELECTEDVALUE ( 'Table'[summary_date] )
            && [flag] = 0
            && 'Table'[Serial Number] = SELECTEDVALUE ( 'Table'[Serial Number] )
    )
)

result = DATEDIFF([minflagdate],MAX('Table'[summary_date]),DAY)+1

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[LynnG]

Thank you so much!! This worked perfectly!!