Forum Discussion
Determining New Payroll Additions/Removals
The company that I work for has tasked me with making a table that will show people who are added/removed from our payroll each month.
So far, I've tried dividing it into two tables and using a measure to detect for each table (employee names hidden for privacy).
For Recent Payroll Additions, I need the table to be able to detect unique IDs which exist for the current month but not any previous months. For Recent Payroll Removals, I need to be able to detect unique IDs which exist for the previous month but not the current month. Our monthly payroll is updated on the 10th of every month, so I know my general window is about 30 days between updates.
The relevant information I have which I can use to help me with this is a table named 'HeadCounts', which contains the columns [CalendarYear], [PERS_ID], [Employee Class], [MonthNumber], [Year and Month Code], and [Years of Service]. I am new to Power BI so I've been teaching myself DAX as I go, but I haven't been able to find any solution to help me make a working table.
akoster3 , with help from a common dimension, you can simply check is blank and get new
In my case both current prior measures come from same table, you can take them from different table
Consider the logic
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
2 Replies
- amitchandak
Super User
akoster3 , with help from a common dimension, you can simply check is blank and get new
In my case both current prior measures come from same table, you can take them from different table
Consider the logic
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529I went to the step in your tutorial 'Customer Retention Part 1' and tried my best to imitate what's in there. Since there is no sales data tied to this, this is what I have:
MTD =DATESMTD('HeadCounts'[Year and Month Code])LMTD =DATESMTD(DATEADD('HeadCounts'[Year and Month Code],-1,MONTH))New Payroll Additions =sumx(VALUES('HeadCounts'[PERS_ID]),if(ISBLANK('HeadCounts'[LMTD]) && NOT(ISBLANK('HeadCounts'[MTD])),TRUE(),FALSE()))New Payroll Removals =sumx(VALUES('HeadCounts'[PERS_ID]),if(ISBLANK('HeadCounts'[MTD]) && NOT(ISBLANK('HeadCounts'[LMTD])),TRUE(),FALSE()))
I'm not sure exactly why, but it won't let me use these measures as filters on my visual.
