Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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
AlB
Super User
Super User

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

 

 

 

Thanks for your answer @AlB Smiley Happy

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

Any idea ?

 

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

 

 

 

 

@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

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

   

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.