KPI-Average of days between 2 datetime

[poojashribanger]

Hello All,

I am trying to show below formula in KPI .

i need to get max(datetime) and 2nd max datetime for each ID and then the difference between them and then i need to average it out in KPI for all the ID's

Average(max(date)-max(date,2))

Can someone please help me on this.

Table:

Thanks &  Regards,

Poojashri

[v-rongtiep-msft · Accepted solution]

Hi @poojashribanger ,

Pleae have a try.

Create a measure.

measure =
VAR _1 =
    RANKX (
        FILTER ( ALL ( 'table' ), 'table'[id] = SELECTEDVALUE ( 'table'[id] ) ),
        CALCULATE ( MAX ( 'table'[date] ) ),
        ,
        DESC,
        DENSE
    ) //Sorts dates under the same id.
VAR _maxdatre =
    CALCULATE (
        MAX ( 'table'[datetime] ),
        FILTER (
            ALL ( 'table' ),
            'table'[id] = SELECTEDVALUE ( 'table'[id] )
                && _1 = 1
        )
    )
VAR _2nd =
    CALCULATE (
        MAX ( 'table'[datetime] ),
        FILTER (
            ALL ( 'table' ),
            'table'[id] = SELECTEDVALUE ( 'table'[id] )
                && _1 = 2
        )
    )
RETURN
    ( _maxdatre - _2nd ) / 2

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 _ Rongtie

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

[poojashribanger]

Thank You so much for this.

I did some changes to this same expression and i got the expected output.