Group by employeeid, sum and filter by single date which falls between startdate and enddate

[SBSTAE]

Hi,

I'm trying to create a measure that sums the parttime percentages [ptfactor] of employees (grouped by) that were valid on a single selected date [selecteddate] (between [validfrom] and [validuntil]). 

RosterTable

id	employeeid	rosterid	ptfactor	validfrom	validuntil
1	40072	1	1.0000	01-01-11	31-12-11
2	40072	78	0.7778	01-01-12	02-11-14
3	40072	98	0.8889	03-11-14	31-12-14
4	40072	98	0.7778	01-01-15	30-08-15
5	40072	323	0.7778	31-08-15	28-02-17
6	40072	406	0.7778	01-03-17	28-02-18
7	40072	475	0.7778	01-03-18	31-08-20
8	40101	72	0.7222	01-01-11	10-06-12
9	40101	164	0.7222	11-06-12	04-01-15
10	40101	285	0.7778	05-01-15	01-01-40
11	70206	9	1.0000	01-01-11	01-01-40
12	80308	76	0.7778	01-01-11	13-05-12
13	80308	158	0.7778	14-05-12	31-08-13
14	80308	76	0.7778	01-09-13	31-08-14
15	80308	244	0.7778	01-09-14	31-05-15
16	80308	308	0.7778	01-06-15	21-06-15
17	80308	313	0.7778	22-06-15	30-06-17
18	80308	425	0.8889	01-07-17	21-01-18
19	80308	463	0.8889	22-01-18	01-01-40
20	140601	2	1.0000	01-01-11	31-12-11
21	140601	3	1.0000	01-01-12	31-08-14
22	140601	7	1.0000	01-09-14	01-01-40
23	150159	8	1.0000	01-01-11	12-06-16
24	150159	376	1.0000	13-06-16	31-08-18
25	150159	480	1.0000	01-09-18	01-01-40

I'm struggling with this the last couple of days. Does someone know how I can accomplish the above? Much appreciated!

[Icey]

Hi @SBSTAE ,

Is this problem solved?

Best Regards,

Icey

[HotChilli]

Can you explain what this part means 'Group by employeeid', please.

Perhaps it would be best if you show the expected output from selecting a date e.g. 30 April 2012.

Thanks for the data by the way.  Many people don't post it.

[SBSTAE]

@HotChilli 

Thank you for your reply. Group by means that per employeeid only one ptfactor will be included in the sum of all ptfactor that meet the selected date. Because the start- and enddate don't overlap the group by employeeid is probably not necessary. 

RosterTable

id	employeeid	rosterid	ptfactor	validfrom	validuntil
1	40072	1	1.0000	01-01-11	31-12-11
2	40072	78	0.7778	01-01-12	02-11-14
3	40072	98	0.8889	03-11-14	31-12-14
4	40072	98	0.7778	01-01-15	30-08-15
5	40072	323	0.7778	31-08-15	28-02-17
6	40072	406	0.7778	01-03-17	28-02-18
7	40072	475	0.7778	01-03-18	31-08-20
8	40101	72	0.7222	01-01-11	10-06-12
9	40101	164	0.7222	11-06-12	04-01-15
10	40101	285	0.7778	05-01-15	01-01-40
11	70206	9	1.0000	01-01-11	01-01-40
12	80308	76	0.7778	01-01-11	13-05-12
13	80308	158	0.7778	14-05-12	31-08-13
14	80308	76	0.7778	01-09-13	31-08-14
15	80308	244	0.7778	01-09-14	31-05-15
16	80308	308	0.7778	01-06-15	21-06-15
17	80308	313	0.7778	22-06-15	30-06-17
18	80308	425	0.8889	01-07-17	21-01-18
19	80308	463	0.8889	22-01-18	01-01-40
20	140601	2	1.0000	01-01-11	31-12-11
21	140601	3	1.0000	01-01-12	31-08-14
22	140601	7	1.0000	01-09-14	01-01-40
23	150159	8	1.0000	01-01-11	12-06-16
24	150159	376	1.0000	13-06-16	31-08-18
25	150159	480	1.0000	01-09-18	01-01-40

Selecting april 30th 2012 will sum the purple ptfactor's totalling 5.2778‬. 

[HotChilli · Accepted solution]

Thanks for that.  I noted that there was no overlap per employee.

You could create a disconnected Date table with CALENDARAUTO.

Create a slicer with this Date table.

Create a measure like this

Measure = VAR _selDate = SELECTEDVALUE(Dates[Date])
RETURN 
    CALCULATE(SUM('Table'[ptfactor]), FILTER('Table',_selDate >= 'Table'[validfrom] && _selDate < 'Table'[validuntil]))

and you could put the measure on a card OR if you want to see the employeeid results too, put the employeeid and measure in a table visual

[SBSTAE]

@HotChilli 

I'm getting blank value as a result.

It does work when I use:

FTE =

VAR _selectedDate = SELECTEDVALUE ('DatumTabel'[Date])

RETURN CALCULATE (  SUM ( dashHrRosters[ptfactor] );    dashHrRosters[validfrom] <= _selectedDate;  dashHrRosters[validuntil] >= _selectedDate)

The problem was that my date table was linked in the datamodel. I didn't know that just unlinking it was the solution. 

How can I for example add another filter than just the date filter? An example: I have a another table with employmenttypes with an [employeeid, employment type, validfrom, validuntil]

Can I add a filter that only includes employmenttypes of a certain kind in the calculation of ptfactor? I thought of adding a calculated column to the rosters table but I read that I should avoid using calculated columns if the same can be accomplished with a measure. 

[HotChilli]

There's nothing wrong with adding a calculated column to the rosters table. If the validfrom and validuntil are the same in the employmenttypes tables as the rosters table  then it probably makes sense.  If they are the same then you will probably want to reshape your tables a bit so that you are not holding the same data in different tables.

[SBSTAE]

@HotChilli 

Thank you. The validfrom and validuntil are different in the other table, much longer periods. Does this change the possibility to use a calculated column? 

[SBSTAE]

In case someone had the same issue of filtering different tables and joining these for a measure.
I have two tables, one with employmenttypes (in a certain period) and one with rosters (what parttime percentage does an employee work in a certain period). I needed to calculate the total parttime percentage of all internal employees (internal employees are those with emptype 101 or 111) on a specific date.

Personeelsnummer = employeeid

The following worked for me:

FTE3 =

VAR _selDate = SELECTEDVALUE ('DatumTabel'[Date])

RETURN

CALCULATE(SUM(dashHrRosters[ptfactor]);

FILTER (

CROSSJOIN (

ALL ( dashHrEmploymenttypes );

ALL ( dashHrRosters )

);

(dashHrEmploymenttypes[emptype] = 101 || dashHrEmploymenttypes[emptype] = 111) && dashHrRosters[personeelsnummer] = dashHrEmploymenttypes[personeelsnummer] && dashHrRosters[validfrom] <= _selDate && dashHrRosters[validuntil] >= _selDate && dashHrEmploymenttypes[validfrom] <= _selDate && dashHrEmploymenttypes[validuntil] >= _selDate

)

)

[HotChilli]

Excellent stuff.  You took what I wrote and improved it for your more specialised case.  Well done.