cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
theitguy
Helper I
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

DepartmentCodeOrganisationClusterDepartmentName
12345ABC MechatronicsSalesInHouseService
123456ABC MechatronicsSalesFieldService
98765ABC ElectronicsTechnologyDevelopment

 

Participants

Participant_IDNameDepartmentCodeHire_Date
1John Doe1234501-11-2017
2Max Mustermann1234501-11-2017
3John Smith1234501-11-2017
4Jan Janssen1234501-11-2017
5John Blow12345601-11-2017
6Juan Pérez12345601-11-2017
7Matti Meikäläinen12345601-11-2017
8Jean Dupont9876501-11-2017
9John Brown1234501-12-2017

 

Learning Sessions of the Participants

Participant_IDLaunch_History_idLaunch_DateSeconds_Spend
12098417.11.17444
12327117.11.1724
12327319.11.17974
42097023.11.17439
42330123.11.171
52093505.11.1784
52181905.11.1728
52181717.11.1772
52098317.11.17373
62098129.11.1710
82098703.11.17225
82337817.11.17148
82338617.11.174
92476403.12.17567

 

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

table_power_bi2.PNG

OrganisationClusterDepartmentName#LearnersInParticipants#LearnersInSessionsSum_of_Seconds_SpendAverage_Time_Per_Active_UserAverage_Time_Per_Users
ABC MechatronicsSalesInHouseService521882941376,4
ABC MechatronicsSalesFieldService32567283,5189
ABC ElectronicsTechnologyDevelopment11377377377

#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
v-chuncz-msft
Community Support
Community Support

@theitguy,

 

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.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@theitguy,

 

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.

View solution in original post

theitguy
Helper I
Helper I

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors