Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
thisguy
Advocate I
Advocate I

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

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. 

 

 

1 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"))

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

@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"))

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"))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.