Dax formular with Weekly average to work with new employee

[MaleneL]

Hi

A have this table that I want to calculate average meeting pr. Week from until know it is working great with the formular:

Number of meetings average =

    AVERAGEX(VALUES(Dim_Calender[Week]), [# of meetings] +0)

But the problem is, if an employee starts in the middle of the year, it will still calculate from week 1.

Can somebody help?

year	Week	employee no.	# of meetings	Start date	opload date
2024	1	11	22	01-02-2010	07-01-2024
2024	1	22	55	03-03-2014	07-01-2024
2024	1	33	6	01-02-2020	07-01-2024
2024	1	44	8	01-02-2010	07-01-2024
2024	1	55	22	03-03-2014	07-01-2024
2024	1	66	66	01-02-2020	07-01-2024
2024	1	77	32	01-02-2010	07-01-2024
2024	1	88	85	03-03-2014	07-01-2024
2024	1	99	41	01-02-2020	07-01-2024
2024	2	11	22	01-02-2010	14-01-2024
2024	2	22	55	03-03-2014	14-01-2024
2024	2	33	6	01-02-2020	14-01-2024
2024	2	44	8	01-02-2010	14-01-2024
2024	2	55	22	03-03-2014	14-01-2024
2024	2	66	66	01-02-2020	14-01-2024
2024	2	77	32	01-02-2010	14-01-2024
2024	2	88	85	03-03-2014	14-01-2024
2024	2	99	41	01-02-2020	14-01-2024
2024	3	11	22	01-02-2010	21-01-2024
2024	3	22	55	03-03-2014	21-01-2024
2024	3	33	6	01-02-2020	21-01-2024
2024	3	44	8	01-02-2010	21-01-2024
2024	3	55	22	03-03-2014	21-01-2024
2024	3	66	66	01-02-2020	21-01-2024
2024	3	77	32	01-02-2010	21-01-2024
2024	3	88	85	03-03-2014	21-01-2024
2024	3	99	41	01-02-2020	21-01-2024
2024	3	100	5	15-01-2024	21-01-2024
2024	4	11	22	01-02-2010	28-01-2024
2024	4	22	55	03-03-2014	28-01-2024
2024	4	33	6	01-02-2020	28-01-2024
2024	4	44	8	01-02-2010	28-01-2024
2024	4	55	22	03-03-2014	28-01-2024
2024	4	66	66	01-02-2020	28-01-2024
2024	4	77	32	01-02-2010	28-01-2024
2024	4	88	85	03-03-2014	28-01-2024
2024	4	99	41	01-02-2020	28-01-2024
2024	4	100	5	15-01-2024	28-01-2024

Sincerely Malene

[Anonymous]

Hi @MaleneL ,

Try to modify your formula like below:

Weekly Average Meetings1 = 
VAR EmployeeStartWeek =
    WEEKNUM ( MIN ( 'YourTable'[Date] ) )
VAR CurrentWeek =
    MAX ( 'Dim_Calendar'[Week] )
RETURN
    AVERAGEX (
        FILTER (
            VALUES ( 'Dim_Calendar'[Week] ),
            'Dim_Calendar'[Week] >= EmployeeStartWeek
                && 'Dim_Calendar'[Week] <= CurrentWeek
        ),
        MAX(Dim_Calendar[# of Meetings]) + 0
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MaleneL]

Hi Adam

I have tried the formula, but unfortunately it does not work – It is the last part:

Dim_Calendar[# of Meetings]

That doesn’t work.

I have tried to modify it by change it to:

Weekly Average Meetings1 =

VAR EmployeeStartWeek =   

WEEKNUM ( MIN ( 'YourTable'[Date] ) )

VAR CurrentWeek =    MAX ( 'Dim_Calendar'[Week] )

RETURN    AVERAGEX (       

FILTER (           

VALUES ( 'Dim_Calendar'[Week] ),           

'Dim_Calendar'[Week] >= EmployeeStartWeek               

&& 'Dim_Calendar'[Week] <= CurrentWeek        ),       

MAX(Dim_Calendar[Week], [# of Meetings) + 0    )

But the result is not what I want (and I am not sure what the result is ;O))
If I have an employee starting in Week 22:

I would like the result to be:

16/9 = 1,7778                             (week 22-30)

But this is what I get:

Average Full year = AVERAGEX(VALUES(Dim_Calender[Week]), [# of meetings] +0)

Average New = The corrected formular above

Can you see what I am doing wrong?

[MaleneL]

Hi
Because AVERAGEX ignores blanks when averaging, and if an employee have weeks with zero meetings I need to have that week in the calculation:

AVERAGEX(VALUES(Dim_Calender[Week]), [# of meetings] +0)

Is doing what I want to – looking at numbers of meeting pr. Employe. Divided with number of weeks in the year. The problem is if I have an employe starting in - let’s say - week 3, I want the formular to calculation this employe average from that week and not week 1.

Average for Employee 11 = (22+22+22+22)/4

Average for Employee 22 = (55+55+55+55)/4

Average for Employee 01 = (44+44+0+44)/4                              - have no meetings in week 3 (+0 fix this)

Average for Employee 100 = (5+5)/2                                            - Starts week 3

Hope this gives meaning.

[amitchandak]

@MaleneL , I think you should not use +0

AVERAGEX(VALUES(Dim_Calender[Week]), [# of meetings] )

Can you please share the expected output and mismatch