Forum Discussion

lucianovazbh's avatar
lucianovazbh
Frequent Visitor
3 years ago

average age in year

Hello friend,
I need a big help from you!!!
I have a visual where it has a dynamic age calculation and also an age group, all of this dynamically as shown in the attached example.
I need to get the average age over the years. Example, if in 2016 there are 11 employees, and I have their age (what is the average age in that year?
I've tried everything and I couldn't.
OBS I don't want to use calculated column, it has to be measured.PBIX example 

 




Need Help

4 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    3 years ago

    Hey lucianovazbh ,

     

    please make access to your pbix file anonymous if I try to download the file I'm asked to request access, I will not do that.

     

    Regards,

    Tom

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    3 years ago

    Hey lucianovazbh ,

     

    I created this measure:

    average age = 

var currentYear = CALCULATE( MAX( 'Dcalendar'[Ano] ) )
var lastDayOfCurrentYear = DATE( currentYear , 12 , 31 )
return
AVERAGEX(  
    FILTER(
        CALCULATETABLE(
            'Table'
            , ALLSELECTED( 'Table' )
        )
        , YEAR( 'Table'[Atualizacao] ) <= currentYear
    )
    , var dateOfBirth = CALCULATE( MAX( 'Table'[Nascimento] ) )
    return
    DATEDIFF( dateOfBirth , lastDayOfCurrentYear , YEAR )
)

    This allows me to create this visual:

    You can see how the first employee is aging through the year 1946 - 1958, but then a 2nd employee reduces the average age.

     

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

    • Lucianovaz's avatar
      Lucianovaz
      Frequent Visitor
      3 years ago

      TomMartens 
      Thanks for the feedback,
      Only the employee must be considered for age calculation where: a table[update]=max(table[update]) and table[Status]="N", as per the example file.xlsx.
      otherwise, it must accept segmentation such as average by gender,
      unfortunately I still can't solve it.
      The average should be like the image below and the xlsx file.


      Example XLSX