cancel
Showing results for
Did you mean:
Frequent Visitor

## Evolution of calculated measure

Hello,

I have an employee table with a start date column and an end date column, and a "work" table with the employee id, a date and a count of hours.
I also have a "Year" segment that allows me to filter the report by year.

I did this measure to calculate the number of employees in contract, who worked at least 5 hours during the selected year :

```CountOfWorkingPerson =
CALCULATE(
DISTINCTCOUNT('Person'[PersonId]);
FILTER(
'Person';
(
YEAR('Person'[EndDate])>=SELECTEDVALUE('YearList'[Year])
|| ISBLANK('Person'[EndDate])
)
&& YEAR('Person'[StartDate])<=SELECTEDVALUE('YearList'[Year])
);
FILTER(
Work;
YEAR(Work[Date])=SELECTEDVALUE('YearList'[Year])
&& SUM(Work[Hours])>=5
)
)```

But I would like to have, over the last 5 years of the filter, the evolution of this measure.

How to do this under PowerBI?

Sample data:

Person Table
============
ID,StartDate,EndDate
1,2011-01-01,null
2,2012-02-07,2014-01-21
3,2013-03-14,2016-07-11
4,2014-04-23,null
5,2015-05-05,2015-07-14
6,2016-06-09,2019-12-12
7,2017-07-30,null
8,2018-08-21,null
9,2019-09-15,null

Work Table
==========
ID,EmployeeID,Date,Hours
1,1,2012-07-25,8
2,1,2014-05-26,4
3,3,2014-05-24,6
4,3,2015-02-14,7
5,5,2015-06-06,3
6,5,2015-06-07,4
7,7,2017-08-30,5
8,7,2018-09-14,1
9,9,2019-10-12,7

Expected results when filter on 2018 :
- 2014 : 1
- 2015 : 2
- 2016 : 0
- 2017 : 1
- 2018 : 0

Florent

5 REPLIES 5
Super User

## Re: Evolution of calculated measure

Try this:

1. Create a 1-to-many unidirectional relationship between  'Person'[ID]  and 'Work'[EmployeeID]. To avoid later confusion, I would recommend changing the name of Person[ID] to Person[EmployeeID]. You have a 'Work'[ID] column that seems to be an index column and not the Employee ID.

2. Set YearList[Year] in the rows of a matrix visual (or in the axis of any other type of chart)

3. Set this measure (based on your code) in values of the matrix:

```New_CountOfWorkingPerson =
VAR _SelectedYear =
SELECTEDVALUE ( 'YearList'[Year] )
RETURN
COUNTROWS (
FILTER (
FILTER (
'Person';
(
YEAR ( 'Person'[EndDate] ) >= _SelectedYear
|| ISBLANK ( 'Person'[EndDate] )
)
&& YEAR ( 'Person'[StartDate] ) <= _SelectedYear
);
CALCULATE (
SUM ( 'Work'[Hours] );
YEAR ( 'Work'[Date] ) = _SelectedYear
) >= 5
)
) + 0```

Frequent Visitor

## Re: Evolution of calculated measure

Unfortunetaly with your measure I get the same result as with mine :

Any idea ?

Super User

## Re: Evolution of calculated measure

@florentbignier

OK, so what is the problem? From what I see in your data 2 is the correct result for 2015, isn't it? Plus it's the expected result that you posted initially.

Let's wwalk it over.  For 2015 you have the following rows in the 'Work' table:

4, 3, 2015-02-14,7
5, 5, 2015-06-06,3
6, 5, 2015-06-07,4

which means that we have 7 hours for employee with ID 3 and  7 (3+4) hours for employee with ID 5. So that is two employees, which is what the measure returns. Both employees are also "in contract" in 2015 according to the Person table so, where is the issue?

Frequent Visitor

## Re: Evolution of calculated measure

@AlB

What I would like to obtain is the evolution of the last 5 years compared to the selected year.
For example, when I select 2018, I would like this result:

Sorry if my initial request was not quite clear

Super User

## Re: Evolution of calculated measure

@florentbignier

OK. got it. The problem with that is that you want to use the same field in the slicer and in the rows of the matrix visual. Once you select one year in the slicer the matrix will be filtered and only that year will be shown in the matrix. I do not think you can override that behavior. I thought of a few alternatives:

1. Editing the interaction between the slicer and the matrix and eliminating it. This won't work because without the interaction we won't be able to harvest the value in the slicer through ALLSELECTED( ) or similar. Or so it appears.

2. How about simply using the 'Between' option in the slicer to select the years that you want shown?

3. You could also ditch the slicer and use the visual level filters of the matrix. Two filters on YearList[Year]:

a) One to show the years less than or equal to the year you would have selected in the slicer (2018 in your example)

b) Another one to show the top 5.  So 2018-2014 will be shown

4. Lastly, you could create another one-column table, exactly the same as YearList, and use that one on the slicer (keep the current one on the rows of the matrix). Then you would avoid the issues described above. The measure would need some minor modification.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

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

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 291 members 3,183 guests
Recent signins: