How to calculate average

[Steph83]

Good day,

I closed my topic too quickly

Here was my demand:

How to calculate average age of a group of persons in XX years?
I have a group of 300 people ("Artists") for whom I have their date of birth ("date of birth").

I would like to know the average age of this group on a specific date that I would select in a dashboard filter.

In fact my data base gathered all persons, but all are not available all the time. So I need to know, among the persons who are there, at a specific date, the average age of the persons

John proposed the following:

Average age =
VAR ReferenceDate =
SELECTEDVALUE ( 'Slicer date'[Date] )
VAR Result =
AVERAGEX ( Artists, DATEDIFF ( Artists[Date of birth], ReferenceDate, YEAR ) )
RETURN
Result
where 'Slicer date'[Date] is the column you are using on the slicer.

Something is missing. I have well the average of the list of persons at a selected date, however, it gives the average of all the persons whatever the filter applied on the page (gender, above 30....). How can I include the selected filters in the formula so as to calculate the average on the selected persons only?

Thanks in advance for you support

Stephane

[tamerj1]

Hi @Steph83 

do you mean you have a column in your rmtable that specifies the date when the record was added? And you want the date slicer selected date to filter all thoses records which were added on or before the selected date?

on the other hand I believe the provided measure shall not have any issue with your page and report filters. It should work with these filters just fine. 

[Steph83]

Hi @tamerj1 ,

I have 2 tables:

1 - One table which lists the people month by month

2 - One table which contains all the data of the persons

Both table are linked by the person (1 to multiple)

[tamerj1 · Accepted solution]

Hi @Steph83 
I hope the month column is Date formatted (I called [Date Added] in my code)

Average age =
VAR ReferenceDate =
    SELECTEDVALUE ( 'Slicer date'[Date] )
VAR Result =
    AVERAGEX (
        CALCULATETABLE (
            Artists,
            People[Date Added] <= ReferenceDate,
            CROSSFILTER ( Artists[Person ID], People[Person ID], BOTH )
        ),
        DATEDIFF ( Artists[Date of birth], ReferenceDate, YEAR )
    )
RETURN
    Result

[Steph83]

Hi @tamerj1 ,

It works perfectly, thanks for your support!

Stephane