Forum Discussion

Powerful's avatar
Powerful
Frequent Visitor
4 years ago
Solved

Released and Joined Employees count

Hi, I'm new to PowerBI DAX. I have connected data source to a folder where list of employees data dump is placed on regular basis (not daily).  Table name called Emp Data, columns are Data Dump Date...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Powerful 

     

    You can try the following methods.

    Measure:

    Count1 = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Emp ID] = SELECTEDVALUE ( 'Table'[Emp ID] )
            && [Data Dump Date] <= SELECTEDVALUE ( 'Table'[Data Dump Date] )
    )
)
    Count2 = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Emp ID] = SELECTEDVALUE ( 'Table'[Emp ID] )
            && [Data Dump Date] >= SELECTEDVALUE ( 'Table'[Data Dump Date] )
    )
)
    Joined = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Data Dump Date] = MAX ( 'Table'[Data Dump Date] )
            && [Count1] = 1
    )
)
    Released = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Data Dump Date] = MIN ( 'Table'[Data Dump Date] )
            && [Count2] = 1
    )
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
4 years ago

Hi, Powerful 

 

You can try the following methods.

Measure:

Count1 = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Emp ID] = SELECTEDVALUE ( 'Table'[Emp ID] )
            && [Data Dump Date] <= SELECTEDVALUE ( 'Table'[Data Dump Date] )
    )
)
Count2 = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Emp ID] = SELECTEDVALUE ( 'Table'[Emp ID] )
            && [Data Dump Date] >= SELECTEDVALUE ( 'Table'[Data Dump Date] )
    )
)
Joined = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Data Dump Date] = MAX ( 'Table'[Data Dump Date] )
            && [Count1] = 1
    )
)
Released = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Data Dump Date] = MIN ( 'Table'[Data Dump Date] )
            && [Count2] = 1
    )
)

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

Powerful's avatar
Powerful
Frequent Visitor
4 years ago

Hi v-zhangti,

Thanks this is working fine.

 

I have more than 2 dates in my data.

I have put date slicer also. I'm trying to do this for only 2 dates as you have done, those 2 dates should come from slicer MIN and MAX. I trying to modify the measures to get the results for more than 2 dates but unable to do it. Please help me..

Appreciate your support!!