Forum Discussion
[Seek Help] How to calculate the value difference between the max date and the previous date?
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
1 Reply
- v-yuta-msft
Community Support
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.