[Seek Help] How to calculate the value difference between the max date and the previous date?

[TomLU123]

Hi Expert, 

We are developing report to show the week headcount trend. The data is refreshed on a weekly basis. And the [Headcount Changes compared to last week] is one of the indicators we are tracking. Therefore, we wish to just compare the headcount difference between ①the Max Effective Date user choose in slicer and ②the previous Effective Date of Max Effective date. It may not be necessary the Min of the date chose in the slicer.

Example as below:

Effective Date	Employee
1/1/2019	A
1/1/2019	B
1/1/2019	C
1/1/2019	D
1/1/2019	E
3/18/2019	A
3/18/2019	B
3/18/2019	C
3/18/2019	D
3/18/2019	E
3/18/2019	F
3/25/2019	A
3/25/2019	B
3/25/2019	C
3/25/2019	D
3/25/2019	E
3/25/2019	F
3/25/2019	G
3/25/2019	H

For example,

On 3/4/2019, the headcount is 5.

On 3/11/2019, the headcount is 6.

On 3/18/2019, the headcount is 8

If the user choose 3/4/2019 to 3/11/2019, the [Headcount Changes compared to last week] is 6-5=1

If the user choose 3/11/2019 to 3/18/2019, the [Headcount Changes compared to last week] is 8-6=2

If the user choose 3/4/2019 to 3/18/2019, the[Headcount Changes compared to last week] is 8-6=2 (instead of 8-5=3)

Is it possible to achieve that by using DAX?

Thank you so much!

Best regards,

Tom

[v-yuta-msft]

@TomLU123 ,

"

On 3/4/2019, the headcount is 5.

On 3/11/2019, the headcount is 6.

"

Still not very clear about this logic, could clarify more details about why headcount before 3/4 is different from headcount from 3/11?

Community Support Team _ Jimmy Tao

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