Showing results for 
Search instead for 
Did you mean: 
florentbignier Frequent Visitor
Frequent Visitor

Evolution of calculated measure


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 =
            || ISBLANK('Person'[EndDate])
        && YEAR('Person'[StartDate])<=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

Work Table

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


Thanks in advance,


Super User
Super User

Re: Evolution of calculated measure

Hi @florentbignier


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] )
        FILTER (
            FILTER (
                    YEAR ( 'Person'[EndDate] ) >= _SelectedYear
                        || ISBLANK ( 'Person'[EndDate] )
                    && YEAR ( 'Person'[StartDate] ) <= _SelectedYear
            CALCULATE (
                SUM ( 'Work'[Hours] );
                YEAR ( 'Work'[Date] ) = _SelectedYear
            ) >= 5
    ) + 0




florentbignier Frequent Visitor
Frequent Visitor

Re: Evolution of calculated measure

Thanks for your answer @AlB Smiley Happy

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

Any idea ?


Super User
Super User

Re: Evolution of calculated measure



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?  





florentbignier Frequent Visitor
Frequent Visitor

Re: Evolution of calculated measure



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 Smiley Sad

Super User
Super User

Re: Evolution of calculated measure



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.             


Helpful resources

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 291 members 3,183 guests
Please welcome our newest community members: