Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Rolling YTD Turnover Rate

I am using a calendar table to calculate Average Employee Counts by month based on a termination date (may be blank) and hire date. I have the following calculation three times with minor variations, one for the first of the month, one for the 15th and one for the end of month. 

 

Active On First = 
VAR firstOfMonth =
   DATE(YEAR(MAX( 'Date'[Date] )),MONTH(MAX( 'Date'[Date] )), 1)
RETURN
    CALCULATE (
        COUNTROWS ( 'Average EE Listing' ),
        FILTER (
            'Average EE Listing',
            ( 'Average EE Listing'[Last Hire Date] <= firstOfMonth
                && ('Average EE Listing'[Termination Date1] >= firstOfMonth || 'Average EE Listing'[Termination Date1] = 0 )
        )
    ))

 

 

To get the monthly average, I sum all three variations (Active on First, Active on Fifteenth, Active on Last) and divide by 3.

 

Next, I calculate turnover rate. I count my list of terms for that month and divide that by the average employee count: 

 

Count Monthly Terms = 
VAR firstOfMonth =
   DATE(YEAR(MAX( 'Date'[Date] )),MONTH(MAX( 'Date'[Date] )), 1)

VAR lastOfMonth =
   DATE(YEAR(MAX( 'Date'[Date] )),MONTH(MAX( 'Date'[Date] )), DAY(EOMONTH(MAX( 'Date'[Date] ),0)))

RETURN

    CALCULATE(
        COUNTROWS ( 'Employee Termination List' ),
        FILTER (
            'Employee Termination List',
            ( 'Employee Termination List'[Termination Date] >= firstOfMonth
                && ('Employee Termination List'[Termination Date] <= lastOfMonth )
        )
    ))

 

Monthly Turnover Rate = 
FORMAT(DIVIDE('Employee Termination List'[Count Monthly Terms], 'Average EE Listing'[Month Count Average EE]), "0.0%")

 

 

 

The problem is this only works when I filter by one month on my slicer. How do I get the same info, the turnover rate, for YTD? For example, lets say I wanted a line graph that shows the turnover rate for each month. I tried selecting multiple months on the slicer but the turnover rate card then just evaluates to blank and I am unable to enter the turnover rate measure in the line graph.

Need Help

12 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Greg, apologies for my lack of knowledge regarding Power BI. I am completely new to it and trying to apply a real world example to learn it. Can you briefly describe the measure below? I can't quite figure out how I would apply this to me needs.

       

      TITHW_TotalYTDHW = 
VAR __MaxYear = MAX('Years'[Year])
VAR __MaxMonth = MAX('Months'[MonthSort])
VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month]))
RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear && [MonthSort] <= __MaxMonth),[Value])
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        7 years ago

        The first line gets the max value of a Year column, so in theory that is the latest year in the table which would be 2018. Think of this as current year in the context of the visual. The next line gets the current month in the context of the visual. The third line gets the data for all years and all months and puts it into a table variable. The forth line, reading inside out, filters the table variable to only keep the data for the current year and only the months that are less than or equal to the current month (this is the year to date part). It then does a SUM for the [Value] on those filtered rows. 

         

        Basic year-to-date calculation stuff which is what it sounded like you wanted.