Dynamic Measure Average

[Anonymous]

Hi All

I am working on HR data and trying to calculate average age which nees to be dynamic, so far I have created a calculated column that displays age based on slicer date, however this is not working properly and not dynamuic, therefore, i created a measure that displays the age correctly, now I need to calculate average age based of the measure and it is not working properly, please see below , could yo please help me with this?

Age - Measure = ENDOFMONTH(Dates[Date])- SUM(Employee[BirthDate])

Avcerage Age - Measure = 
AVERAGEX(Employee, ENDOFMONTH(Dates[Date])- SUM(Employee[BirthDate]))

Average Age = 
         CALCULATE(AVERAGE(Employee[Age - Calculated Column]),
                FILTER (
                        Employee,
                        (Employee[commencementDate] <= max ( 'Dates'[Date] )
                            && Employee[TerminationDate] == BLANK ())  ||Employee[commencementDate]<= max ( 'Dates'[Date] )
                            && Employee[TerminationDate] >= MAX ( 'Dates'[Date] ))
                    )

Last Date = ENDOFMONTH(Dates[Date])

Age - Calculated Column = DATEDIFF(Employee[BirthDate], [Last Date],YEAR)

Number Employees by Date = 
COUNTROWS(
   FILTER (
        Employee,
        (Employee[commencementDate] <= [Last Date]
                    && Employee[TerminationDate] == BLANK ()  || Employee[commencementDate] <= [Last Date]
            && Employee[TerminationDate] > [Last Date])
    ))

[bhelou]

Hello , 
can you share some sample data , its hard to figure it out directly like this , 

1- try to make a measure and make it as a reference to get back to it  :  MAX date =  Max(dates[Date]) 
2- create a measure fo the age of each emp :  AGE = DATEDIFF(employee[birthdate],[max date],year)
3 - make average :  average_age = average(employee[age])

4 - here to make the average by month : 

av_month = calcualte( average(employee[age]),
filter(
all(dates),
dates[date] <= max(dates[date])))


//// this will calculate the average based on the max date i the dates table and filters data by all dates including slected date slicer , make sure birthdate contains valid dates and termination dates is blank for emp who are sstill working , if these are not true you need to modify it , 

give it a try and please accept as a solution if it applies with you , kindly share a sample pbix file , this is intresting to work with  , i faced issue like this before 

[Anonymous]

Hi @bhelou  

The DAX you mentioned above is similar to what I already have and the result is not dynamic as there is no relation ship between dates and employee table. 

But I worked out the dax i needed and it is working fine for what I need, see below;

Average Age = 
VAR _table = FILTER(ALL(Employee), 
                               (Employee[commencementDate] <= [Last Date]
                               && Employee[TerminationDate] == BLANK ()  || Employee[commencementDate] <= [Last Date]
                                 && Employee[TerminationDate] > [Last Date]))
RETURN
AVERAGEX(_table, (DATEDIFF(Employee[BirthDate], [Last Date], YEAR)))