Forum Discussion

Steph83's avatar
Steph83
Frequent Visitor
3 years ago
Solved

How to calculate average

  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 who...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
Steph83
Frequent Visitor
3 years ago

Hi tamerj1 ,

It works perfectly, thanks for your support!

Stephane