Count of current month active minus count of previous month active plus count of current month exit

[thisguy]

I have a FACT_GTO table used to present a distinct count of CandidateID monthly (via a DIM_calendar table), Also in the data for each employee is a status field showing Active or exit reason: Active, Completed, Withdrawn or Cancelled.

I want to calculate new starters each month for a seperate graph automatically by the following:  newstarters = (current month count of active EmployeeID  - previous months count of active EmployeeID) + count of Employee ID filtered by exit status: (Completed, withdrawn and Cancelled) to then show this count in a seperate visual. 

I've tried using Previousmonth but can't seem to get the right DAX expression - most of the articles i have serached are to sum up totals not count. 

heres MY dax attempt: 

NewStarters = CALCULATE(COUNT(FILTER(GTO[Candidate ID] ="Active"))) - CALCULATE(COUNT(FILTER(GTO[Candidate ID] ="Active"))),PREVIOUSMONTH(DATESMTD('Calendar'[Date]))) + COUNTAX(GTO,FILTER(GTO,GTO[New Status] <> "Active"))

any help would be appreciated. 

[Anonymous]

When you say "previous months count of active EmployeeID" - that would mean all the previous months cumulative sum?

Can you share more details on the tables structures?

Does the Fact table Fact_GTO store the count of candidate or each CandidateID?

In which table you are storing the Active/Exit Reason for each Candidate? How are these linked?

Where is the Start Date?

[amitchandak]

@thisguy , Try like

NewStarters = CALCULATE(COUNTrows(FILTER(GTO[Candidate ID] ="Active"))) - CALCULATE(COUNTrows(FILTER(GTO[Candidate ID] ="Active")),PREVIOUSMONTH('Calendar'[Date])) + COUNTAX(GTO,FILTER(GTO,GTO[New Status] <> "Active"))

[thisguy · Accepted solution]

hi Amitchandak - Your code didn't quite work but got me on the right path to get it working thankyou - actual measure: 

NewStarters = (COUNTROWS(FILTER(GTO,GTO[New Status] = "Active"))

- CALCULATE(COUNTROWS(FILTER((GTO),GTO[New Status] = "Active")),PREVIOUSMONTH('Calendar'[Date])))

+ COUNTROWS(FILTER(GTO,GTO[New Status] <> "Active"))