calculate the sum of users based on their start and end contracts

[Anonymous]

Hello Community,

I want to create a line chart showing the number of users based on their start and end contracts for exemple I have on January 15 users on february 13 because two their contracts ended.

I have two datasets one is users and the other one is date

I create two relationships one is active contract_start and date_actual and one is inactive contract_end and date_actual

then I tried to this measure = 

CALCULATE(COUNT(USER[user_id])) - CALCULATE(COUNT(USER[user_id]),USERELATIONSHIP(TEMPS[date_actual],USER[date_fin_ctrt]))

but doesn't work

any solutions ??

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

First, please don't create any relationship between TEMPS and USER and create a measure as below:

Measure = 
VAR _curdate=MAX('TEMPS'[date_actual])
return 
CALCULATE (
   DISTINCTCOUNT( 'USER'[user_id] ),
    FILTER (
        'USER',
         (
            'USER'[date_debut_ctrt] <=_curdate
                && (
                    'USER'[date_fin_ctrt] >= _curdate
                     || ISBLANK ( 'USER'[date_fin_ctrt] )
                )
        )
    )
)​

Best regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[amitchandak]

@Anonymous , refer if my blog on a similar topic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.