Sum between dates when month is a dimension

[Marcos_S]

Hello, I am not able to solve this challenge: I have a table of people (see attachment) whith Employee ID, Profile, Date In, Date Out, and Number of employees. If we know the employee, we have an ID and the number of employees is 1. If we only know the profile we will need, we do not have an ID and the number of empoyees can be any number.

I need to show a graph per month and per profile, with the number of active employees: so I have to sum the number of employees each month if Date In is before the month and Date Out is after the month.

Any idea?

Many thanks.

[Sergey_Shuklin · Accepted solution]

Hello!

Is this solution suitable for you?

Script improving:

LOAD Year, 
     [Employee ID], 
     Profile, 
     [Date In],
     [Date Out],
     AddMonths(MonthStart([Date Out]),-1) as rep_date,
     [Number of employees]
FROM
People.xlsx
(ooxml, embedded labels, table is [Initial table]);

[Marcos_S]

Thanks. I have to test it. I did not realized that I can load the data in a different way in order to show the graph.

Many thanks