Calculating the Average Value of the Earliest and Latest Date the user chose

[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 headcount of the erlist date's headcount and latest date's the user choose, and then divided by 2. 

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

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

Date	Headcount
6/1/2018	5000
6/2/2018	4500
6/3/2018	4800
6/4/2018	4900
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 experssion to achieve that?

Many thanks for your help!

Best regards,

Tom

[TomMartens · Accepted solution]

Hey,

assuming that the user selects the dates using a date slicer, this measure does what you are looking for:

Measure = 
var selectedDates = ALLSELECTED(Table2[Date])
var minDate = MINX(selectedDates,'Table2'[Date])
var maxDate = MAXX(selectedDates,'Table2'[Date])
return
(CALCULATE(SUM(Table2[Headcount]),'Table2'[Date] = minDate)
+ CALCULATE(SUM(Table2[Headcount]),'Table2'[Date] = maxDate))/2

Here is a little screenshot:

Regards,

Tom

[TomLU123]

Hi Tom,

Just a further question:what if some of the date's headcount is empty. 

We still wish to create a measure to calculate the Average Headcount of the period by Sumimg the not empty headcount of the earliest date and not empty headcount of latest date 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/5/2018 to 6/11/2018, the Average Headcount of the period = (5100+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 modify the experssion to achieve that?

Many thanks!

Best regards,

Tom

@TomMartens