cancel
Showing results for
Did you mean:
Visitor

## 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/2018 First 2 25/01/2018 First 3 21/01/2018 First 4 01/02/2018 First 1 03/02/2018 Second 2 14/03/2018 Second 1 05/02/2018 Third

In this example, I want to know the date difference between criteria but in the same ID.
The formula is = DateDifference ID = Second - First
In excel that would be:
ID 1 = 03/02/2018-18/01/2018 = 16 days
ID 2 = 14/03/2018-25/01/2018 = 48 days
ID 3 = Blank (there is no second criteria)
ID 4 = Blank (there is no second criteria)
So, the average of that is 32.

Just like the table under:

 DateDIFF (Second - First) ID 1 16,00 ID 2 48,00 ID 3 Blank ID 4 Blank Average 32,00

I want to do the same for (Third-Second), and so on...

How Can I create a Measure of that?

Thanks ikn advance, I am stuck in this problem for a few days now....

PS: I want create that in DAX not in Power Query.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Calculating average of date difference between specific rows

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

## Re: Calculating average of date difference between specific rows

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,471)