YTD Annual Turnover

[yaman123]

Hi, 

I need to calcule the YTD Annual Turnover and show this in a pie chart per site. 

The way HR calculate this is: 

SUM of monthly leavers past 12 months / AVERAGE Headcount past 12 months 

I have a date table with a Month Year column to select the period to report on. So i would like to show the YTD values from the chosen month - 11 if possible. 

[v-shex-msft]

HI @yaman123,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,
Xiaoxin Sheng

[yaman123]

Hi @v-shex-msft 

I dont have anything on this as it will be a new report. 

I have a table with employee name, id, start date, end date

I have another date table (not linked) with date, month year

I would like to show the YTD turnover using the above calculation. 

If i select Month Year Dec 2020, then it should give me the rolling headcount average for Dec 2020 plus previous 11 months and the same for leavers 

[v-shex-msft]

HI @yaman123,

You can try to use the following measure expression if it meets your requirement:

Measure =
VAR sEnd =
    MAX ( Data[Date] )
VAR range =
    CALENDAR ( DATE ( YEAR ( sEnd ) - 1, MONTH ( sEnd ), DAY ( sEnd ) ), sEnd )
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS (
            FILTER ( ALLSELECTED ( Employee ), [End Date] IN range ),
            "Year", YEAR ( [End Date] ),
            "Month", MONTH ( [End Date] )
        ),
        [Year],
        [Month],
        "MonthTotal", COUNT ( Employee[id] )
    )
RETURN
    AVERAGEX ( summary, [MonthTotal] )

Regards,
Xiaoxin Sheng

[yaman123]

Hi @v-shex-msft 

This doesnt work for me. It is giving me the same figures for any month i select. 

[v-shex-msft]

Hi @yaman123,

Can you please share a pbix file with some dummy data and expected results to test? I think it will help us with testing.

Regards,

Xiaoxin Sheng

[yaman123]

Hi @v-shex-msft 

I have this measure to calculate the headcount: 

Employee Count =
VAR selectedDate = MAX('Date'[Date])
RETURN
SUMX('TABLE1',
VAR employeeStartDate = [DATE_OF_EMPLOYMENT]
VAR employeeEndDate = [DATE_OF_LEAVING]
RETURN IF(employeeStartDate <= selectedDate &&
OR(employeeEndDate >= selectedDate, employeeEndDate=BLANK()
),1,0)
)

I have this measure to calculate the leavers:

Monthly Leavers =
var currentdate = MAX('Date'[Date])
RETURN
CALCULATE(DISTINCTCOUNT('TABLE1'[EMP_NO]),
'TABLE1'[DATE_OF_LEAVING] >= DATE(YEAR(currentdate),MONTH(currentdate),1)
&& 'TABLE1'[DATE_OF_LEAVING] <= DATE(YEAR(currentdate),MONTH(currentdate)+1,1)-1)

So if i select to run the report for Dec 2020, i need the rolling totals for the employee count from Feb 2020 - Dec 2020 and rolling totals for leavers from Feb 2020 - Dec 2020. Once we have these, then i will need to calculate the turnover with this formula: 

SUM(ROLLING TOTAL LEAVERS)/AVERAGE(ROLLING TOTAL EMPLOYEE COUNT)

[v-shex-msft]

Hi @yaman123,

I modify my expressions to add calculation for total leaver and rolling employee count, you can try this if it helps:

Measure =
VAR currDate =
    MAX ( 'Date'[Date] )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Date' ),
        [Date].[Year],
        [Date].[MonthNo],
        "remain",
            COUNTROWS (
                FILTER ( ALLSELECTED ( 'Table' ), [End] > currDate || [End] = BLANK () )
            ),
        "leave", COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [End] <= currDate ) )
    )
RETURN
    SUMX ( summary, [leave] ) / AVERAGEX ( summary, [remain] )

Regards,
Xiaoxin Sheng

[yaman123]

Hi @v-shex-msft 

This is giving me the same figures for each site. 

Can you help with a dax measure for a rolling total for headcount? 

From my employee count measure (previosuly sent) i get the below results. I would like to show the rolling total column e.g for Oct 19, the total would be 407, Nov 19 - 613 etc..

[v-shex-msft]

HI @yaman123,

Since I still not so sure about your table structure, can please share some dummy data/pbix file (keep raw data structure) and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@yaman123 , What I typically do is I create date from month year and use time intelligence

Example "Jan -2021"

date = "01-" & [Month year] //Change data type to date.

Now you can join this with a date table.

example of YTD

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.