Average of Measure

[Anonymous]

Hi, 

In my historical project table I have created the following measure:

TimeSinceModification =

VAR _CurrentDate = TODAY()

VAR _LastIdModification =

    CALCULATE(

            MAX(Audit[ModifiedOn].[Date]),

            FILTER(

                ALLSELECTED(Audit),

                Audit[Id] = MAX(Audit[Id])))

VAR _TimeSinceLastModification =

    DATEDIFF(_LastIdModification,_CurrentDate,DAY)

RETURN

   _TimeSinceLastModification


The measure is showing number of days since the last modification for each project in the table. 
Now I have to create an measure showing the average number of days since last modification. I tried different AverageX,Sumx e.g.
But everytime it is summing incorrect. Doe's anyone have an idea how to solve this problem?
@SpartaBI 

[Jihwan_Kim]

Hi,

I am not sure how your dataset looks like, but please try something like below and check whether it works for your dataset.

expected measure: =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( 'projecttable'[projectID] ),
        "@datediff", [TimeSinceModification]
    ),
    [@datediff]
)

[Anonymous]

Hi Jihwan,

I have tried to replicate the structure of my data tabel and hope it gives better meaning now:

E.g. you can see that I have filtered 6 unique project. But project 260 have 2 different modification date and it should only calculate the Lates modified. 
So here the correct Average should be (roundet) 5

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.

Expected avg measure: =
VAR _maxdatebyIDtable =
    CALCULATETABLE (
        Data,
        TREATAS (
            GROUPBY (
                Data,
                Data[ID],
                "@maxdate", MAXX ( CURRENTGROUP (), Data[Name ModifiedOn] )
            ),
            Data[ID],
            Data[Name ModifiedOn]
        )
    )
RETURN
    DIVIDE (
        SUMX ( _maxdatebyIDtable, Data[Days since modification] ),
        COUNTROWS ( _maxdatebyIDtable )
    )

[Anonymous]

Hi, 

I have implemented your solution and it works perfectly.

Thanks