Forum Discussion

GertRiet's avatar
GertRiet
Regular Visitor
8 years ago
Solved

Calculating average of date difference between specific rows

Hello everyone! I am having trouble in creating a specific logic. The ideia is find the average of the diference of the days between specific rows. For example: IDDateCriteria 1 18/01/20...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    8 years ago

    Hi GertRiet

     

    You may try to create measures as below:

    Diff =
VAR a =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Date] < MAX ( Table1[Date] )
                && Table1[ID] = MAX ( Table1[ID] )
        )
    )
RETURN
    DATEDIFF ( a, MAX ( Table1[Date] ), DAY )
    Average =
AVERAGEX (Table1, [Diff] )

    Regards,

    Cherie

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
8 years ago

Hi GertRiet

 

You may try to create measures as below:

Diff =
VAR a =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Date] < MAX ( Table1[Date] )
                && Table1[ID] = MAX ( Table1[ID] )
        )
    )
RETURN
    DATEDIFF ( a, MAX ( Table1[Date] ), DAY )
Average =
AVERAGEX (Table1, [Diff] )

Regards,

Cherie