cancel
Showing results for
Did you mean:
Highlighted
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:

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

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

Best Regards,

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

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

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

Best Regards,

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

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

## Re: Calculated Measure and Averages

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?

Any further help wold be much appreciated!

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

Announcements

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 94 members 1,816 guests
Recent signins: