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
