Calculating Active Employees from Most Recent Record
Hello, I am trying to analyze workforce metrics over time. I have a fact table that has a record for each time an employee file has changed. It has at least one record for every employee. This table includes unique employee ID, Start date, division, Term Date, Effective Date (when the record was created) and a calculated column of Record Order- which numbers the rows for each employee, with 1 being the first record, 2 being the second and so on. I also have dimension tables for Date, Org Structure (Division/Section) Demographics I need to count active employees at a point in time where Start date <= SelectedDate, TermDate is blank, or > SelectedDate. The issue I am running into is; if an employee has been active in two divisions at different times during the selected period, they are Distinct Counted in each division. This means that the overall organization numbers are accurate, BUT they will count as an employee in each division when that slicer is applied. I need to be able to use just the most recent record for each unique employee- as that most recent record will show their current location. Example Data: PS ID start date Division Term Date RecordOrder Eff Date 1 1/1/2020 A null 1 5/18/2020 1 1/1/2020 A null 2 5/19/2020 1 1/1/2020 B null 3 7/11/2020 1 1/1/2020 B null 4 10/3/2020 2 1/2/2020 A 4/2/2021 1 1/2/2020 2 1/2/2020 A 4/2/2021 2 10/3/2020 3 1/1/2020 A null 1 1/1/2020 3 1/1/2020 B null 2 10/3/2020 In the above data set, using a date slicer on say 10/4/2020, I would want to return a distinct count (3) active employees, 2 in division B. 1 1/1/2020 B null 4 10/3/2020 2 1/2/2020 A 4/2/2021 2 10/3/2020 3 1/1/2020 B null 2 10/3/2020 The first approach I took was to use an 'events in progress' methodology 1. Measure to count all employees based on Start Date 2. Measure to distinct count all active employees at point in time where start date<= selected date, and term date is blank or after Selected date this returns accurate counts for the whole organization- but when appling division slicers, it counts employees as a member of every division they had been active in at any point. So, if someone was in A and later B- they would show up when slicing by A or B. I need to return only the most recent record. The next approach I tried was to identify the MaxRecordOrder of each ID, and filter the active employees measure where RecordOrder=MaxRecord Order: MaxRecordOrder = CALCULATE( MAX(Employee_Fact[RecordOrder]), ALLEXCEPT(Employee_Fact, Employee_Fact[PS ID], 'DateTable') ) Active Employees= VAR EndDatePerVisual = MAX('DateTable'[Date]) VAR RESULT = CALCULATE( DISTINCTCOUNT(Employee_Fact[PS ID]), REMOVEFILTERS('DateTable'), FILTER( Employee_Fact, Employee_Fact[Start Date] <= EndDatePerVisual && ( Employee_Fact[Term Date] > EndDatePerVisual || ISBLANK(Employee_Fact[Term Date]) ) && Employee_Fact[RecordOrder] = [MaxRecordOrder] ) ) RETURN RESULT This approach seems to correctly identify the max RecordOrder for each ID, responsive to the date slicer- but still counts rows where the MaxRecord order <> RecordOrder- so employees are still showing up in counts for multiple divisions! I've also attempted the second approach using the max effective date in place of the RecordOrder calculated column, with no success. Any help would be appreciated!
DAX calculate Previous month lost customer revenue
Hi guys, I am having trouble calculating the previous month lost customer revenue. My model relies on a single way relationship between thr fact table and the time table, so that is not possible to change. I would like to display the measure "Churn_lost_$" as shown in the picture below. Thank you very much! 🤙 You can donload the model here: https://drive.google.com/file/d/1MKFmtBIm1Z-yJ0--FhVRuIrZqWVV166c/view?usp=sharingHELP!!! calculating LOST CUSTOMERS
HI friends, I am creating a customer lost calculation and I'm able to calculate it in the subtotals correctly but unable to place it in the row level as the customer does not appear that month. I show you: Also I would like to calculate the money that the customer 493 spent in the past month. But appearing in february in it's new line. Please fins attached the PBI file HERE https://drive.google.com/file/d/13Bdh978nHmrAZWro06cOMaQ2J4ggTV0e/view?usp=sharing Thank you very much!!!
Detect Past Month lost customer
Hi, I'm trying to find the customers that I lost from the previous month. So far I've been able to calculate it individually but I'm stuck with the subtotals. The model is a single table inndicating if a customer is active or not. The expected outcome is the measure "Lost". The measure for past months looks like this (works fine): Past Month =: VAR var1 = CALCULATE( SUM(Consulta1[Active]), PREVIOUSMONTH(Consulta1[Date]) ) RETURN var1 The measure for lost looks like this (the subtotals don't work fine): Lost = VAR var1 = SUM(Consulta1[Active]) - [Past Month] RETURN IF( var1>=0, BLANK(), var1 ) Any idea? Thanks!!
Converting Excel formula to DAX
Hello all. I am having trouble creating a measure requirement from the client that calculates their yearly churn rate. Excel formula and expected output Cumulative: ID Date Customers Churn rate Cumulative Formula C 2018 52 0.99 51.48 =C2*D2 C 2019 182 0.98 228.8104 =(C3+E2)*D3 C 2020 178 0.95 386.46988 =(C4+E3)*D4 C 2021 252 0.98 625.700482 =(C5+E4)*D5 C 2022 302 0.94 872.038453 =(C6+E5)*D6 Currently, this is my measure: #Cumulative = CALCULATE ( SUM ( [Customers] ) * SUM ( [Churn Rate] ), FILTER ( ALL ( 'Table' ), Table[ID] = MAX ( Table[ID] ) && Table[Year] <= MAX ( Table[Year] ) ) ) Is it possible to recreate the requirement dynamically? I'm at wits' end on understanding the logic flow. Thank you. syncinatti
Churn of customers acquired in previous period
Hi guys and gals, Working for a subscription service I am trying to figure out the number and value of susbcriptions and customers who ended their subscription after pre-defined periods (e.g. 1 month, 2 months etc.). For this I do have a Subscription table with subscription details (subscription ID, customer ID, start date, end date, payment frequency, year value), a Transaction table with all detailed transactions (subscription ID, customer ID, transaction ID, transaction date, transaction amount) and a standard Calendar table. In the model, the date of this date table is connected to the start date in the Subscription table and the transaction date in the Transaction table. The DAX measures I wrote are like: ChurnSubscriptionLT030Days# = CALCULATE( [TotalSubscriptions], DATESINPERIOD(Subscription[startdate], MAX(Subscription[startdate]),-30,DAY), DATESINPERIOD(Subscription[enddate], MAX(Subscription[startdate]),-30,DAY) ) When viualising these measures in a bar graph with the measure on Y-axis and the calendar month (MONTH('Calendar'[Date]) on X-axis the numbers are incorrect. I am sure the solution is simple so I am hoping you can help me out.Previous period (not month) active users
I was working on cohort analyses and created periods for retention analyses. The period column in datasheet was calculated through the Invoice date. Now I need to calculate churn rate % for this I need the previous period (not month) active users. Data attached for your reference
