Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
any help would be appreciated.
Solved! Go to Solution.
hi Amitchandak - Your code didn't quite work but got me on the right path to get it working thankyou - actual measure:
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?
@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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |