Calculate the Average Headcount of the period

[TomLU123]

Dear experts,

I have a dataset like below. We wish to create a measure to calculate the Average Headcount of the period by Sumimg the not empty headcount of the erlist date and not empty headcount of the latest date's the user choose, and then divided by 2. 

For example, if the user choose 6/3/2018 to 6/10/2018, the Average Headcount of the period=  (5100+4500)/2. 

If the user choose 6/6/2018 to 6/11/2018, the Average Headcount of the period = (5200+5000)/2

Date	Headcount
6/1/2018	5000
6/2/2018	4500
6/3/2018
6/4/2018
6/5/2018	5100
6/6/2018	5200
6/7/2018	5400
6/8/2018	5200
6/9/2018	4900
6/10/2018	4500
6/11/2018	5000
6/12/2018	4700

How should we write the expression to achieve that?

Many thanks!

Best regards,

Tom

[Zubair_Muhammad · Accepted solution]

@TomLU123

You can use this MEASURE

Measure =
VAR MyMin =
    CALCULATE (
        MIN ( [Headcount] ),
        CALCULATETABLE ( FIRSTDATE ( Table1[Date] ), Table1[Headcount] <> BLANK () )
    )
VAR MyMax =
    CALCULATE (
        MAX ( [Headcount] ),
        CALCULATETABLE ( LASTDATE ( Table1[Date] ), Table1[Headcount] <> BLANK () )
    )
RETURN
    ( MyMin + MyMax )
        / 2

[TomLU123]

Hi Zubair,  @Zubair_Muhammad

Just a further quetsion, what if the raw data set is like below. The database will refresh the active employee ID in following table once a day. The "Report Date" is the refresh date. 

For example, on 6/1/2018 and 6/2/2018, they are empty which means there are no employees.

On 6/3/2018, there are 4 employees joined.

On 6/4/2018, there are 4 employeess (no one join or left).

On 6/5/2018, there are 3 employees (one left).

I sitll wish to create a to create a measure to calculate the Average Headcount of the period by Sumimg the not empty headcount of the erlist date and not empty headcount of the latest date's the user choose, and then divided by 2.

For example, if the user choose 6/1/2018 to 6/5/2018, the Average Headcount of the period= (4+3)/2.

If the user choose 6/3/2018 to 6/4/2018, the Average Headcount of the period = (4+4)/2

Report Date	Employee ID
6/1/2018
6/2/2018
6/3/2018	111
6/3/2018	222
6/3/2018	333
6/3/2018	444
6/4/2018	111
6/4/2018	222
6/4/2018	333
6/4/2018	444
6/5/2018	111
6/5/2018	222
6/5/2018	333

How should we modify the Expression to achieve that?

Many thanks!

Best regards,

Tom