cancel
Showing results for
Did you mean:
Frequent Visitor

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

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(

)

(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(

)

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

1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!