cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GuillemXII Regular Visitor
Regular Visitor

Calculated Measure and Averages

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: 

 

Data...png

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculated Measure and Averages

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.4-1.PNG

 

Best Regards,

Eads

 

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

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Calculated Measure and Averages

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.4-1.PNG

 

Best Regards,

Eads

 

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

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GuillemXII Regular Visitor
Regular Visitor

Re: Calculated Measure and Averages

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!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 94 members 1,816 guests
Please welcome our newest community members: