Get Average of days for each ID that has been changed based on date_created

[Pandadev]

Hi I am trying to find a way to get an average of how long it takes to change data for each ID code in a large data set.

An example for ID 026A is as follows , it was created on 01/01/2020

01/01/2020

24/01/2020 23 days between updates

02/02/2020 9 days between updates

15/03/2020 42 days between updates

30/04/2020 46 days between updates

Average is 24 days between updates

I would then Like to be able to get the average of all the averages for each ID code.

Is this possible in PowerBI

[amitchandak]

@Pandadev 

New column like

last_date = datediff(maxx(filter(table,[ID]=earlier([ID]) && [Date]<earlier([Date])),[Date]),[Date],day)

This measure can also give the last date and then you can take diff. View by ID, Date Calendar has been used

Last Day Non Continous = CALCULATE(Max('Table'[Date]),filter(all(Table),Table[Date] =MAXX(FILTER(all(Table),Table[Date]<max(Table[Date])),Table[Date])))

[Pandadev]

I am getting an error saying Date Created cannot be found or cannot be used in this expression , when adding the measure.

The column formula worked , but it does not seem to give the correct values , does it take into account dividing by how many ID's there are to get the average date diff

[v-yingjl]

Hi @Pandadev ,

The calculated column like @amitchandak  created will get the right datediff and you can create an average measure to calculate average datediff based on each ID:

Average =
CALCULATE (
    DIVIDE ( SUM ( 'Table'[Column] ), COUNTROWS ( 'Table' ), BLANK () ),
    ALLEXCEPT ( 'Table', 'Table'[ID] )
)

The final result will like this:

Best Regards,
Yingjie Li

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

[Pandadev]

Is it possible if date created = date modified , the the date diff would be date craeted compared to todays date , as otherwise the value is zero