Calculate Sum Total filter by contract (start and end) date, per person

[StatStories_Lon]

Dear All,

My name is Ruud and I am preparing a dashboard for an external company. Because of privacy, I have prepared a similar data set that shows the problem that I have at the moment.

Context: for this company I am trying to calculate the total sum of hours worked, and the total sum of target hours that one has to work in a specific period.

The first one is not a problem, however the second one I have troubles with by the way they register their data.

The problem: In the targeted hours, they include hours for each day for the whole year (except for weekend days). The problem occurs when a person does not work the whole year, for the reason that he or she leaves the company or enters the company at a specific moment during the year.

What I would like to have:

I want a measure that calculates the sum of total targeted hours per person with their specific contract- start and end date. In other words, if a person started after the first date in the calendertable, then the targeted hours present before this date should not be included.

Likewise, if the end date is before todays date, then the hours after the contract_end date to todays moment should now be included.

The file:

In the Data I focused my problem on user ‘A1’. His contract date starts at 05-03-2020 and end beyond our todays date. However, the targeted hours start counting from 05-01-2020. Therefore, there are two months included in the sum of targeted hours that should not be there.

Thank you for your time,

Cheers,

Ruud Scheijen

https://drive.google.com/file/d/1KfPdIVPzgqQr-YXyGGNz5kaRYn_4JrI3/view?usp=sharing 

[camargos88]

Hi @StatStories_Lon ,

Try this measure:

_Teste =

VAR _min = CALCULATE(MIN(Dim_Contract[Start_Date]); ALLEXCEPT(Dim_Users;Dim_Users[UserId]))

VAR _max = CALCULATE(MAX(Dim_Contract[End_Date]); ALLEXCEPT(Dim_Users; Dim_Users[UserId]))

VAR _userID = SELECTEDVALUE(Dim_Contract[UserId])

RETURN CALCULATE(SUM('Fact_Target Hours'[Target Hours]); FILTER(ALL('Fact_Target Hours'); 'Fact_Target Hours'[medewerker_id] = _userID && 'Fact_Target Hours'[Datum] >= _min && 'Fact_Target Hours'[Datum] <= _max)

)

But if users can have more than 1 contract, this is not gonna work. In this case, we need to consolidate every contract (using summarize) before sum them.

Ricardo

[kentyler]

I have a question. Can a User_ID have more than one contract..? they do not in your sample data. Which suggests that Dim_Users and Dim_Contracts could be combined... giving you a star schema instead of a snowflake. That will probably make writing the measure you want easier.

[StatStories_Lon]

Thank you for your reaction.

It appears that there are users with more than one contract. Thus one userid with multiple start & end dates.

Thank you for your reaction.

I did not know, but it appears that there are users with more than one contract. Thus one userid with multiple start & end dates.

** In this case, we need to consolidate every contract (using summarize) before sum them.**

How would this work then?

First summarizing the userid, contractstartdate, contractenddate, and in turn apply your suggested steps?

[camargos88]

I tried this code:

SUMMARIZE(Dim_Contract; Dim_Contract[ContractId]; Dim_Users[UserId]; Dim_Contract[Start_Date]; Dim_Contract[End_Date]; "Qtde Hours";

CALCULATE(SUM('Fact_Target Hours'[Target Hours]); FILTER(ALL('Fact_Target Hours');

'Fact_Target Hours'[medewerker_id] = EARLIER(Dim_Users[UserId]) &&

'Fact_Target Hours'[Datum] >= EARLIER(Dim_Contract[Start_Date]) &&

'Fact_Target Hours'[Datum] <= EARLIER(Dim_Contract[End_Date])

))

)

However, I see you have this:

ContractId	Start_Date	End_Date	UserId
4	3 janeiro, 1991	sábado, dezembro 31, 2022	A4
6	5 janeiro, 1991	sábado, dezembro 31, 2022	A4

Will both contracts be applied for the targets ? 

Target	medewerker_id	Datum	Target Hours
69	A4	domingo, março 8, 2020	6

[StatStories_Lon]

Well this is not possible in the real case. The first contract_end_date will be before the second contract_start_date. 

This is probably a mistake I made when creating the sample data.

**

SUMMARIZE(Dim_Contract; Dim_Contract[ContractId]; Dim_Users[UserId]; Dim_Contract[Start_Date]; Dim_Contract[End_Date]; "Qtde Hours";

**

In your example what is "Qtde Hours?

[camargos88]

Hi @StatStories_Lon ,

This is just as alias for this formula:

CALCULATE(SUM('Fact_Target Hours'[Target Hours]); FILTER(ALL('Fact_Target Hours');

'Fact_Target Hours'[medewerker_id] = EARLIER(Dim_Users[UserId]) &&

'Fact_Target Hours'[Datum] >= EARLIER(Dim_Contract[Start_Date]) &&

'Fact_Target Hours'[Datum] <= EARLIER(Dim_Contract[End_Date])

Ricardo

[StatStories_Lon]

Thank you for your help so far.

I tried your code, but Power Bi shows me this message:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".

Do you know why it shows me this?

[camargos88 · Accepted solution]

Hi @StatStories_Lon ,

Try this measure:

Measure =

VAR _tbl = SUMMARIZE(Dim_Contract; Dim_Contract[ContractId]; Dim_Users[UserId]; Dim_Contract[Start_Date]; Dim_Contract[End_Date]; "Qtde Hours";

CALCULATE(SUM('Fact_Target Hours'[Target Hours]); FILTER(ALL('Fact_Target Hours');

'Fact_Target Hours'[medewerker_id] = EARLIER(Dim_Users[UserId]) &&

'Fact_Target Hours'[Datum] >= EARLIER(Dim_Contract[Start_Date]) &&

'Fact_Target Hours'[Datum] <= EARLIER(Dim_Contract[End_Date])

))

)

RETURN SUMX(_tbl; [Qtde Hours])

Ricardo

[StatStories_Lon]

Thank you very much! This was the solution!

May you have a very nice day and week 🙂

Ruud

[StatStories_Lon]

I occured another problem with this measure.:
It seems that I cannot filter it by a date component.  For example, when I want to see the "Total Target Hours" per month, this will not filter it accordingly and just shows the sum of this measure.

Is it possible that it can't be filtered because it is based on date columns?