Measure for net gain/loss

[rogerdea]

Hello, i'm trying to calculate the net gain or loss using two different date columns.  One column is 'join date' and the other is called 'leave date'.  Join date always has a value and leave date might not if someone is still active.

I'm not sure how to do this in DAX as i need to COUNT distinct ID's, for both date columns and then calculate the difference between each yearly value:

Any ideas?

thanks

[harshnathani · Accepted solution]

Hi @rogerdea ,

You will need to unpivot the columns JoinDate and LeaveDate.

Click on Unpivot Columns

After unpivoting your table will look like below.

Create a Calculated Column

Year = YEAR('Table'[Date])

Create a Measure

Net Gain/Loss =

var _DistinctCount_Joining_by_year = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Join/Leave] = "JoinDate"))

var _DistinctCount_Leave_by_year = CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[Join/Leave] = "LeaveDate"))

RETURN

_DistinctCount_Joining_by_year -_DistinctCount_Leave_by_year

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[speedramps]

Hi roger

Why does 1212, 2099 and 1313 rejoin in overlapping periods and how do you want these overlaps counted please?