cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TomLU123 Regular Visitor
Regular Visitor

[Seek help] How to calculate the value difference on two dates?

Hi Expert, 

 

I have a table like below which shows the actice workers as of two dates.

I would like to create a measure to calculate the difference of headcount between these two dates. 

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

 

For example,

On 1/1/2019, the headcount is 5.

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

Therefore, the [change of headcount] is 6 -5 =1. 

 

Is it possible to achieve that by using DAX?

 

Many thanks!


Best regards,

Tom

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: [Seek help] How to calculate the value difference on two dates?

Hi @TomLU123 

Try this measure in a card visual. You can change the dates of interest in the code. This could also be done through slicers.

 

 

Measure =
VAR _Date1 = DATE ( 2019, 01, 01 )
VAR _Date2 = DATE ( 2019, 03, 18 )
VAR _HeadCountDate1 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        Table1[Effective Date] = _Date1
    )
VAR _HeadCountDate2 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        Table1[Effective Date] = _Date2
    )
RETURN
    _HeadCountDate2 - _HeadCountDate1

 

Super User
Super User

Re: [Seek help] How to calculate the value difference on two dates?

@TomLU123 

Basically what you're saying, if I'm not mistaken, is that you want the earliest and latest dates selected in the slicers, so  just taking the MIN and MAX should suffice. Note it is also necessary to update the filter argument  in the CALCULATEs; the logic is still the same but MIN and MAX are not allowed in the simplified syntax so we have t use FILTER. Please consider kudoing the posts if they are of help.  Table1[Effective Date] is what you'd need on the slicer.

 

Cheers 

 

Measure_v2 =
VAR _Date1 = MIN ( Table1[Effective Date] )
VAR _Date2 = MAX ( Table1[Effective Date] )
VAR _HeadCountDate1 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        FILTER ( ALL ( Table1[Effective Date] ), Table1[Effective Date] = _Date1 )
    )
VAR _HeadCountDate2 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        FILTER ( ALL ( Table1[Effective Date] ), Table1[Effective Date] = _Date2 )
    )
RETURN
    _HeadCountDate2 - _HeadCountDate1

 

6 REPLIES 6
Super User
Super User

Re: [Seek help] How to calculate the value difference on two dates?

Hi @TomLU123 

Try this measure in a card visual. You can change the dates of interest in the code. This could also be done through slicers.

 

 

Measure =
VAR _Date1 = DATE ( 2019, 01, 01 )
VAR _Date2 = DATE ( 2019, 03, 18 )
VAR _HeadCountDate1 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        Table1[Effective Date] = _Date1
    )
VAR _HeadCountDate2 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        Table1[Effective Date] = _Date2
    )
RETURN
    _HeadCountDate2 - _HeadCountDate1

 

TomLU123 Regular Visitor
Regular Visitor

Re: [Seek help] How to calculate the value difference on two dates?

@AlB Thanks for your help!

 

A further question about "This could also be done through slicers."

 

If now there are more than 2 dates and the user can use a slicer to choose the time range they want to see. 

How should I modify the Date Varibles to let the report show the change of headcount between the latest effectice date the user chose and previous 1 effectve date?

 

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 1/1/2019, the headcount is 5.

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

On 3/25/2019, the headcount is 8

 

If the user choose 1/1/2019 to 3/18/2019, the [change of headcount] is 6-5=1

If the user choose 3/18/2019 to 3/25/2019, the [change o f headcount] is 8-6=2

If the user choose 1/1/2019 to 3/25/2019, the [change of headcount] is 8-6=2

 

Is it possible to achieve that by using DAX?

 

Many thanks!


Best regards,

Tom

Super User
Super User

Re: [Seek help] How to calculate the value difference on two dates?

@TomLU123 

Basically what you're saying, if I'm not mistaken, is that you want the earliest and latest dates selected in the slicers, so  just taking the MIN and MAX should suffice. Note it is also necessary to update the filter argument  in the CALCULATEs; the logic is still the same but MIN and MAX are not allowed in the simplified syntax so we have t use FILTER. Please consider kudoing the posts if they are of help.  Table1[Effective Date] is what you'd need on the slicer.

 

Cheers 

 

Measure_v2 =
VAR _Date1 = MIN ( Table1[Effective Date] )
VAR _Date2 = MAX ( Table1[Effective Date] )
VAR _HeadCountDate1 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        FILTER ( ALL ( Table1[Effective Date] ), Table1[Effective Date] = _Date1 )
    )
VAR _HeadCountDate2 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        FILTER ( ALL ( Table1[Effective Date] ), Table1[Effective Date] = _Date2 )
    )
RETURN
    _HeadCountDate2 - _HeadCountDate1

 

TomLU123 Regular Visitor
Regular Visitor

Re: [Seek help] How to calculate the value difference on two dates?

Hi @AlB,  it is really helpful!

 

Just to clarify the scenario, 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. 

 

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)

 

How should we modify the date varible to achieve that?

 

Thank you so much!

 

Best regards,

Tom

TomLU123 Regular Visitor
Regular Visitor

Re: [Seek help] How to calculate the value difference on two dates?

Hi @AlB  , may I ask you for help on this?

 

Thank you so much!

Super User
Super User

Re: [Seek help] How to calculate the value difference on two dates?

@TomLU123 

I hadn't had time.

Try this

 

Measure_v3 =
VAR _Date2 = MAX ( Table1[Effective Date] )
VAR _Date1 = CALCULATE ( MAX ( Table1[Effective Date] ), Table1[Effective Date] < _Date2 )
VAR _HeadCountDate1 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        FILTER ( ALL ( Table1[Effective Date] ), Table1[Effective Date] = _Date1 )
    )
VAR _HeadCountDate2 =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Employee] ),
        FILTER ( ALL ( Table1[Effective Date] ), Table1[Effective Date] = _Date2 )
    )
RETURN
    _HeadCountDate2 - _HeadCountDate1