cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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.
1 REPLY 1
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.

## Helpful resources

Announcements

#### New Ranks and Rank Icons in 2020

Read the announcement for more information!

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)