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/...
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!

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!

      • pintoan's avatar
        pintoan
        Frequent Visitor
        5 years ago

        I have another question, this works fine when selecting 1 id, but i have multiple ID and want a average of time of them. Many thanks for this anyway, is a great upgrade for what i had.