Calculated Measure and Averages

[GuillemXII]

Hello everyone,

For one of our projects, I would like to create a calculated measure the showed how many absences were taken in a company on average for several years (ej. Number Absences /  Number Employees) . However, I'm finding some difficulties in order to obtain the amount of employees per year. 

My "Employees Table" has the following information: 

As you can see, there are two columns with date information: "Date of first Contract" (when he/she was hired) and "Date of Cease" (when he/she was fired or resigned).

In the case of 2017, if I wanted to know how many employees we had back then, I should COUNT everyone who were hired in 2017 or before and which date of cease wasn't inferior to 2017.

Ideally those who were hired/fired in 2017, instead of counting as "1", should just be a percentage of the year worked (Ex. Employee Nº 14 and 15 would just be "0,65" while Nº 5 "0,70"). A guy who worked for 5 days, should'nt count as one that was present for 365. 

I imagine, that I could generate a Calculated Column for each year to obtain that previous value ("2017", "2018", "2019"...) and then inside the calculated measure, put several conditionals indicating which column should be used depending on the year to obtain the average. 

Do you think that might work? Do you have any better idea to get it?

Thank you all!

[v-eachen-msft · Accepted solution]

Hi @GuillemXII ,

At first, you need to create a calendar table.

Calendar =
CALENDAR ( MIN ( 'Table'[Date of first Contract] ), TODAY () )

Then add three new columns in it.

Year =
YEAR ( 'Calendar'[Date] )

StartDate =
DATE ( 'Calendar'[Year], "01", "01" )

EndDate =
IF (
    'Calendar'[Year] = MAX ( 'Calendar'[Year] ),
    MAX ( 'Calendar'[Date] ),
    DATE ( 'Calendar'[Year], "12", "31" )
)

Now create two new measures.

CountPerYearPerEmployee =
VAR selectDateS =
    SELECTEDVALUE ( 'Calendar'[StartDate] )
VAR selectDateE =
    SELECTEDVALUE ( 'Calendar'[EndDate] )
VAR selectYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR cease =
    SELECTEDVALUE ( 'Table'[Date of cease] )
VAR first =
    SELECTEDVALUE ( 'Table'[Date of first Contract] )
VAR se =
    IF (
        YEAR ( first ) > selectYear,
        0,
        IF (
            YEAR ( first ) < selectYear,
            selectDateS,
            IF ( YEAR ( first ) = selectYear, first )
        )
    )
VAR de =
    IF (
        se = 0,
        0,
        IF (
            cease = BLANK ()
                && se <> 0,
            selectDateE,
            IF (
                selectYear > YEAR ( cease ),
                0,
                IF (
                    selectYear < YEAR ( cease )
                        && cease <> BLANK (),
                    selectDateE,
                    IF ( selectYear = YEAR ( cease ), cease )
                )
            )
        )
    )
RETURN
    ROUND (
        IF ( de = 0, 0, DATEDIFF ( se, de, DAY ) )
            / DATEDIFF ( selectDateS, selectDateE, DAY ),
        2
    )

CountPerYear =
SUMX ( VALUES ( 'Table'[Employee Code] ), [CountPerYearPerEmployee] )

Create visuals to show your result. You can use the CountPerYear to calculate the average.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GuillemXII]

Hi @v-eachen-msft , 

That worked wonders! It's magnificent!

That said, I was wondering if it would be possible to bring it to the next level... Because the present year hasn't already ended, when compared with the results of previous years, it will always present lower values: the amount of employees hasn't changed that much, but I'm comparing absences committed during 12 month against 8 months.

Could be possible adding to those measures, a last 12 months average ("N-12") comparable to previous years? 

That would actually give way more information about how things are evolving...

Any further help wold be much appreciated!

Anyways, thank you a lot for what you have already done!