cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bwarner87
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(

        [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.

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1634552349359.png


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.

View solution in original post

1 REPLY 1
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1634552349359.png


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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.