cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomLU123 Member
Member

[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 DateEmployee
1/1/2019A
1/1/2019B
1/1/2019C
1/1/2019D
1/1/2019E
3/18/2019A
3/18/2019B
3/18/2019C
3/18/2019D
3/18/2019E
3/18/2019F
3/25/2019A
3/25/2019B
3/25/2019C
3/25/2019D
3/25/2019E
3/25/2019F
3/25/2019G
3/25/2019H

 

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 1
Community Support Team
Community Support Team

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

@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.