cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GertRiet Visitor
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

118/01/2018First
225/01/2018First
321/01/2018First
401/02/2018First
103/02/2018Second
214/03/2018Second
105/02/2018Third


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 116,00
ID 248,00
ID 3Blank
ID 4Blank
Average32,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
Highlighted
Microsoft v-cherch-msft
Microsoft

Re: Calculating average of date difference between specific rows

Hi @GertRiet

 

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

1.png

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.

View solution in original post

1 REPLY 1
Highlighted
Microsoft v-cherch-msft
Microsoft

Re: Calculating average of date difference between specific rows

Hi @GertRiet

 

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

1.png

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.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

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

November 2019 Community Highlights

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

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)