Forum Discussion

LynnG's avatar
LynnG
Frequent Visitor
3 years ago
Solved

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

 

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 Numbersummary_dateconsecutive days
ABXM6U167361/1/20231
ABXM6U167361/2/20232
ABXM6U167361/3/20233
ABXM6U167361/4/20234
ABXM6U167361/6/20231
ABXM6U167361/7/20232
ABXM6U167361/8/20233
ABXM6U167361/9/20234
ABXM6U167361/10/20235
ABXM6U167361/11/20236
ABXM6U167361/12/20237
ABXM6U166861/1/20231
ABXM6U166861/2/20232
ABXM6U166861/3/20233
ABXM6U166861/7/20231
ABXM6U166861/8/20232
ABXM6U166861/10/20231
ABXM6U166861/11/20232
ABXM6U166861/12/20233
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      LynnG
      Frequent Visitor
      3 years ago

      Thank you so much!! This worked perfectly!!