cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

Helper I

Thank You so much for this.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors