cancel
Showing results for
Did you mean:
Helper I

## Calculation Based On Date From Other Table

Hey guys,

I have the following three tables. Each participant is in a department and creates learning sessions on a per day basis.

Organization Structure

 DepartmentCode Organisation Cluster DepartmentName 12345 ABC Mechatronics Sales InHouseService 123456 ABC Mechatronics Sales FieldService 98765 ABC Electronics Technology Development

Participants

 Participant_ID Name DepartmentCode Hire_Date 1 John Doe 12345 01-11-2017 2 Max Mustermann 12345 01-11-2017 3 John Smith 12345 01-11-2017 4 Jan Janssen 12345 01-11-2017 5 John Blow 123456 01-11-2017 6 Juan Pérez 123456 01-11-2017 7 Matti Meikäläinen 123456 01-11-2017 8 Jean Dupont 98765 01-11-2017 9 John Brown 12345 01-12-2017

Learning Sessions of the Participants

 Participant_ID Launch_History_id Launch_Date Seconds_Spend 1 20984 17.11.17 444 1 23271 17.11.17 24 1 23273 19.11.17 974 4 20970 23.11.17 439 4 23301 23.11.17 1 5 20935 05.11.17 84 5 21819 05.11.17 28 5 21817 17.11.17 72 5 20983 17.11.17 373 6 20981 29.11.17 10 8 20987 03.11.17 225 8 23378 17.11.17 148 8 23386 17.11.17 4 9 24764 03.12.17 567

I have created the following visual out of it where the data is filtered for November 2017:

 Organisation Cluster DepartmentName #LearnersInParticipants #LearnersInSessions Sum_of_Seconds_Spend Average_Time_Per_Active_User Average_Time_Per_Users ABC Mechatronics Sales InHouseService 5 2 1882 941 376,4 ABC Mechatronics Sales FieldService 3 2 567 283,5 189 ABC Electronics Technology Development 1 1 377 377 377

#LearnersInParticipants = DISTINCTCOUNT(Participants_sample[Participant_ID])

#LearnersInSessions = DISTINCTCOUNT(Learning_Results_Single_Sessions_sample[Participant_ID])

What I have done:

I have calculated the average time for all users who actually have sessions.

Average_Time_Per_Active_User = Sum(Learning_Results_Single_Sessions_sample[Seconds_Spend])/DISTINCTCOUNT(Learning_Results_Single_Sessions_sample[Participant_ID])

To compare the departments I have calculated the sum of seconds spend divided by all users in departments, regardless of whether some have sessions or not.

Average_Time_Per_Users = Sum(Learning_Results_Single_Sessions_sample[Seconds_Spend])/DISTINCTCOUNT(Participants_sample[Participant_ID])

What I want:

Actually, the calculation for all department members is currently not correct. My colleague are going to filter for time periods.. Actually, I have to consider the hire-date of the participants then as well.

For example: If I filter for November 2017, the shown average for department "InHouseService" is 376,4 (1882 sum of seconds /5 learners). Actually, this value should be 470,5 --> 1881/4 learners because John Brown was not part of the department in November yet. He started in December. The average has to be calculated with the users who were actually part of the department at the specific point in time.

This is maybe not really a PowerBi question, more a database design question, but can you give me a hint how I can determine the amount of all participants in a department regarding their hire date in combination with the learning sessions?

I would really appreciate some advice.

1 ACCEPTED SOLUTION
Community Support

You may take a look at this discussion.

http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

You may take a look at this discussion.

http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

*PUSH* due to the fact that the topic was blocked because of false spam positive.

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors