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
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
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 Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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