cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
florentbignier Frequent Visitor
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

 

Thanks in advance,

Florent

5 REPLIES 5
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] )
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

 

 

 

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

@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?  

 

 

 

 

florentbignier Frequent Visitor
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:
Capture.PNG

Sorry if my initial request was not quite clear Smiley Sad

Highlighted
Super User
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.