Start Count in Retention Rate viewed in a Matrix with Date Hierarchy

[bwarner87]

DAX Experts,

My overall goal is to calculate the Retention Rate through a Date Hierarchy like Year -> Quarter -> Month. 

Problem -> The part where I’m getting stuck is a “Starting” value which is aimed at getting the count of employees in the previous month period WHILE caring for the Quarter value (count in the last month of the quarter) and Year value (count in the last month of the year). So, If I slice by year, quarter, or month I get the right values when using a set of measures.

Desired Final Results:

• Notice the color-coded number indicates the [Starting Count] matches the previous month’s [Count Active]
• The underlining indicates that the quarter or year value is adopting the value from first month in that period.

December	1172
Year	Count Active	Count Hires	Count Exits	Starting Count	Remaining Count	Retention Rate	Retention Rate PY	Retention Rate YOY%
2021	1207	111	43	1172
Q1	1184	11	0	1172
January	1189	2	0	1172
February	1180	1	0	1189
March	1184	8	0	1180
Q2	1179	34	20	1184
April	1176	2	0	1184
May	1178	13	20	1176
June	1179	19	0	1178
Q3	1207	66	23	1179
July	1188	21	20	1179
August	1187	19	3	1188
September	1207	26	0	1187

(THIS FORMULA IS WORKING THEY WAY I WANT)

Count Active =

VAR CountActive =   

    CALCULATE(

        DISTINCTCOUNT(Turnover[Employee ID]),

        LASTDATE(Turnover[Period])

    )

RETURN

    CountActive

(This is attempt #1 at starting that gets my months correct by my quarter and year aren’t correctly matching my table mockup above)

Starting Count =

    CALCULATE(

        [Count Active], DATEADD(Dates[Date],-1,MONTH)

    )

(This is attempt #2 at starting that gets my Quarter and Year value to use the starting value of the first month in that period. However, now my months are not correctly picking up the previous month )

Starting Count =

VAR Countstart =   

    CALCULATE(

        DISTINCTCOUNT(Turnover[Employee ID]),

        FIRSTDATE(Turnover[Period])

    )

RETURN

    CALCULATE(

        Countstart, DATEADD(Dates[Date],-1,MONTH)

    )

Details:

• DimTable = Dates
• Has columns like: Date, Year, Quarter, Month
• FactTable = Turnover
• Joined on Dates[Date] (1) = Turnover[Period] (Many)
• This is a SNAPSHOT table which is capture all the Active/Leave employees each month and giving them a PERIOD date of the last date of the month.

Turnover Table Structure

Employee ID	Gender Code	EEO Code	DeptDesc	Hire Date	Termination Date	Job Grade	Period
12345	F	2	Dept 10	12/3/2018	1/1/1900	A6U	10/31/2021
12346	F	2	Dept 10	5/9/2011	1/1/1900	T3M	10/31/2021
12347	M	2	Dept 10	5/29/2012	1/1/1900	T3M	10/31/2021
12348	F	2	Dept 10	12/12/2011	1/1/1900	T3M	10/31/2021
12349	F	2	Dept 10	1/30/2012	4/3/2021	T3M	10/31/2021
12350	F	2	Dept 14	10/1/2007	1/1/1900	T3M	10/31/2021
12351	M	2	Dept 14	10/15/2007	1/1/1900	B2M	10/31/2021
12352	F	2	Dept 10	5/16/2016	1/1/1900	B2M	10/31/2021
12345	F	2	Dept 10	12/3/2018	1/1/1900	A6U	11/31/2021
12346	F	2	Dept 10	5/9/2011	1/1/1900	T3M	11/31/2021
12347	M	2	Dept 10	5/29/2012	1/1/1900	T3M	11/31/2021
12348	F	2	Dept 10	12/12/2011	1/1/1900	T3M	11/31/2021
12349	F	2	Dept 10	1/30/2012	4/3/2021	T3M	11/31/2021
12350	F	2	Dept 14	10/1/2007	1/1/1900	T3M	11/31/2021
12351	M	2	Dept 14	10/15/2007	1/1/1900	B2M	11/31/2021
12352	F	2	Dept 10	5/16/2016	1/1/1900	B2M	11/31/2021

Thank you in advance.

[v-yalanwu-msft · Accepted solution]

Hi, @bwarner87 ;

I made a simple example about the count of last month, last quarter and last year. In my example, I used sum, you can change it to discount.

Starting Count =
IF (
    ISINSCOPE ( 'Table'[date].[Month] ),
    CALCULATE ( SUM ( [value] ), PREVIOUSMONTH ( 'Table'[date] ), ALL ( 'Table' ) ),
    IF (
        ISINSCOPE ( 'Table'[date].[Quarter] ),
        CALCULATE (
            SUM ( [value] ),
            PREVIOUSQUARTER ( 'Table'[date] ),
            ALL ( 'Table' )
        ),
        IF (
            ISINSCOPE ( 'Table'[date].[Year] ),
            CALCULATE ( SUM ( [value] ), PREVIOUSYEAR ( 'Table'[date] ), ALL ( 'Table' ) )
        )
    )
)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.