Forum Discussion

pintoan's avatar
pintoan
Frequent Visitor
5 years ago
Solved

lead time and filters

I'm new to power bi. I' m trying to calculate the difference between two dates. Here is a example of my data.

id  operation   date    team
333 A   13/08/21    A
454 C   13/08/21    A
433 F   15/08/21    A
333 C   14/08/21    A
333 D   15/08/21    A
454 A   12/08/21    A
766 A   17/08/21    B
766 D   19/08/21    B
454 B   15/08/21    A
333 B   13/08/21    A

I want to calculate the average time between operation from all ids from one team. I need to be able to filter this by operations and team. example: average time between op A&C from team A. Any help will be very appreciated.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  pintoan ,

     

    Based on my understanding,date for L is 04/08/2021,date for A is 03/08/2021,so the result should be 1.

    If so,check below measure：

    Measure =
VAR _opermax =
    CALCULATE ( MAX ( 'Table'[Operation] ), ALLSELECTED ( 'Table'[Operation] ) )
VAR _opermin =
    CALCULATE ( MIN ( 'Table'[Operation] ), ALLSELECTED ( 'Table'[Operation] ) )
VAR _team =
    SELECTEDVALUE ( 'Table'[TEAM] )
VAR _datemax =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[TEAM] = _team
                && 'Table'[Operation] = _opermax
        )
    )
VAR _datemin =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[TEAM] = _team
                && 'Table'[Operation] = _opermin
        )
    )
RETURN
    DATEDIFF ( _datemin, _datemax, DAY )

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    pintoan , You can get a column, which can give you diff


    time between Ops =
    var _max = maxx(filter(Table, [Team] = earlier([team]) && [Date] < ealier([Date])), [Date])
    return
    datediff(_max,[date], day)

     

    you can create measure based on this diff

     

    • pintoan's avatar
      pintoan
      Frequent Visitor
      5 years ago

      I think i asked the wrong question. This is just a example. If i search for the difference between operation L and A the result is 2. But with the solution you gave i dont have this.

      For example OP C- OP B should be 0 not 1

       

      idOperationTEAMdate
      500AA03/08/2021
      500BA04/08/2021
      500CA04/08/2021
      500DA04/08/2021
      500EA04/08/2021
      500FA04/08/2021
      500GA04/08/2021
      500HA04/08/2021
      500IA04/08/2021
      500JA04/08/2021
      500LA04/08/2021
      500MA04/08/2021
      500NA05/08/2021
      500OA04/08/2021
      500PA05/08/2021
      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi  pintoan ,

         

        Based on my understanding,date for L is 04/08/2021,date for A is 03/08/2021,so the result should be 1.

        If so,check below measure：

        Measure =
VAR _opermax =
    CALCULATE ( MAX ( 'Table'[Operation] ), ALLSELECTED ( 'Table'[Operation] ) )
VAR _opermin =
    CALCULATE ( MIN ( 'Table'[Operation] ), ALLSELECTED ( 'Table'[Operation] ) )
VAR _team =
    SELECTEDVALUE ( 'Table'[TEAM] )
VAR _datemax =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[TEAM] = _team
                && 'Table'[Operation] = _opermax
        )
    )
VAR _datemin =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[TEAM] = _team
                && 'Table'[Operation] = _opermin
        )
    )
RETURN
    DATEDIFF ( _datemin, _datemax, DAY )

        And you will see:

        For the related .pbix file,pls see attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!