cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

## KPI-Average of days between 2 datetime

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))

Table:

Thanks &  Regards,

Poojashri

1 ACCEPTED SOLUTION
Community Support

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
``````

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.

2 REPLIES 2
Community Support

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
``````

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.

Regular Visitor

Thank You so much for this.

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors