Difference Between Dates By Key

[dday9]

I have a table which contains a foreign key column named ResourceId and a date column named Assigned. What I'm wanting to do is get the average length of time between dates for all ResourceIds.

I understand how to do this from a programming point of view:

1. Iterate though every unique ResourceId from a collection that appears more than once.
2. In a nested iteration, iterate through all but the last row where the ResourceId matches the currently (outer) iterated ResourceId.
3. Get the difference between the currently (inner) iterated Assigned date value and the next Assigned date value and add the result to a variable.
4. Once the collection of Assigned values for the respective ResourceId has been fully iterated, take the result of the variable and divide it by the count/length of the collection.
5. Repeat until this has been done for every ResourceId

What I don't understand is how to implement this in Power BI or if it is even possible.

[v-xicai]

Hi @dday9 ,

You may create column and measure like DAX below.

Column: Group rank= RANKX(FILTER(Table1,Table1[ResourceId]=EARLIER(Table1[ResourceId])),Table1[Assigned],,ASC ,Dense)
​
Measure: =
VAR Pdate= CALCULATE(MAX(Table1[Assigned]),FILTER(Table1,Table1[ResourceId]=MAX(Table1[ResourceId])&&Table1[Group rank]=MAX(Table1[Group rank])-1))
VAR DateDiff= DATEDIFF(Pdate, MAX(Table1[Assigned]), DAY)
RETURN
AVERAGEX(Table1, DateDiff)

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dday9]

So ultimately what I wound up doing was creating the following measure:

Measure = 
    VAR earliestDate = MIN(Table1[Assigned])
    VAR latestDate   = MAX(Table1[Assigned])
    RETURN DATEDIFF(earliestDate, latestDate, DAY) / (COUNT(Table1[Assigned]) - 1)

I then applied the following filters to my grid:

• Assigned is not blank
• ResourceId is not blank
• Count of ResourceId is greater than 1

This works so far as it displays the average number of days between rows and it is represented as a decimal rounded to the hundredth place, but what isn't working is the total displayed in the footer of my grid. I'm not sure if this is a bug or what:

Edit - I forgot to mention that when I sort on the last column in ascending order, there are no negative numbers that would cause the total to be 0.00.

[v-xicai · Accepted solution]

Hi @dday9 ,

You may change your measure Average Number of Days like DAX below.

Average Number of Days =
VAR earliestDate =MIN ( Table1[Assigned] )
VAR latestDate =MAX ( Table1[Assigned] )
VAR d =DATEDIFF ( earliestDate, latestDate, DAY ) / ( COUNT ( Table1[Assigned] ) - 1 )
VAR _table =SUMMARIZE ( Table1, Table1[FirstName], "_Value", d )
RETURN
IF ( HASONEVALUE ( Table1[FirstName]), d, SUMX ( _table, [_Value] ) )

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.