Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GertRiet
Regular 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
v-cherch-msft
Employee
Employee

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
v-cherch-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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