Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
This might be very simple to some but I can't figure out how to go about this. FTE for a record = Mandays/Billable Days for that month and the origin country. I've tried 'related' & 'relatedtable' but can't figure out how to specify that it returns the billable days for a particular month and country of origin.
I have a simplified table below. We have multiple projects, worked on by a numer of employees and the number of mandays they work varies. I want to calculate how many FTEs for each record (by country of origin and date worked) by referencing the second table (ignore the third table as that's the original table format).
If anyone can help, I'd be grateful.
Project | Labourer | CountryOfOrigin | DateWorked | Mandays Worked | FTE? |
1 | James | South Africa | 01/01/2018 | 200 | |
2 | Kilner | South Africa | 01/09/2018 | 1000 | |
3 | Magnusson | Ukraine | 01/10/2018 | 250 | |
4 | Edmond | Scotland | 01/03/2018 | 350 | |
5 | Kendrick | South Africa | 01/03/2018 | 800 | |
6 | James | South Africa | 01/01/2018 | 200 | |
7 | Kilner | South Africa | 01/11/2018 | 1000 | |
8 | Magnusson | Ukraine | 01/11/2018 | 250 | |
2 | Edmond | Scotland | 01/01/2018 | 350 | |
3 | Kendrick | South Africa | 01/03/2018 | 800 | |
11 | James | South Africa | 01/05/2018 | 200 | |
10 | Kilner | South Africa | 01/01/2018 | 1000 | |
12 | Magnusson | Ukraine | 01/10/2018 | 250 | |
4 | Edmond | Scotland | 01/08/2018 | 350 | |
5 | Kendrick | South Africa | 01/01/2018 | 800 | |
9 | James | South Africa | 01/11/2018 | 200 | |
9 | Kilner | South Africa | 01/10/2018 | 1000 | |
9 | Magnusson | Ukraine | 01/11/2018 | 250 | |
9 | Edmond | Scotland | 01/11/2018 | 350 | |
10 | Kendrick | South Africa | 01/10/2018 | 800 |
Date | Country of Origin | Billable Days |
Jan-18 | South Africa | 16.00 |
Feb-18 | South Africa | 17.20 |
Mar-18 | South Africa | 19.00 |
Apr-18 | South Africa | 17.20 |
May-18 | South Africa | 19.20 |
Jun-18 | South Africa | 20.00 |
Jul-18 | South Africa | 18.00 |
Aug-18 | South Africa | 15.00 |
Sep-18 | South Africa | 17.00 |
Oct-18 | South Africa | 21.30 |
Nov-18 | South Africa | 20.60 |
Dec-18 | South Africa | 15.00 |
Jan-18 | Ukraine | 17.00 |
Feb-18 | Ukraine | 18.20 |
Mar-18 | Ukraine | 20.00 |
Apr-18 | Ukraine | 18.20 |
May-18 | Ukraine | 20.20 |
Jun-18 | Ukraine | 18.98 |
Jul-18 | Ukraine | 18.25 |
Aug-18 | Ukraine | 14.83 |
Sep-18 | Ukraine | 16.43 |
Oct-18 | Ukraine | 20.00 |
Nov-18 | Ukraine | 19.41 |
Dec-18 | Ukraine | 14.23 |
Jan-18 | Scotland | 18.56 |
Feb-18 | Scotland | 18.53 |
Mar-18 | Scotland | 17.73 |
Apr-18 | Scotland | 17.14 |
May-18 | Scotland | 16.43 |
Jun-18 | Scotland | 20.00 |
Jul-18 | Scotland | 19.25 |
Aug-18 | Scotland | 15.83 |
Sep-18 | Scotland | 17.43 |
Oct-18 | Scotland | 21.00 |
Nov-18 | Scotland | 20.41 |
Dec-18 | Scotland | 15.23 |
Country | Jan-18 | Feb-18 | Mar-18 | Apr-18 | May-18 | Jun-18 | Jul-18 | Aug-18 | Sep-18 | Oct-18 | Nov-18 | Dec-18 |
South Africa | 16.00 | 17.20 | 19.00 | 17.20 | 19.20 | 20.00 | 18.00 | 15.00 | 17.00 | 21.30 | 20.60 | 15.00 |
Ukraine | 17.00 | 18.20 | 20.00 | 18.20 | 20.20 | 18.98 | 18.25 | 14.83 | 16.43 | 20.00 | 19.41 | 14.23 |
Scotland | 18.56 | 18.53 | 17.73 | 17.14 | 16.43 | 20.00 | 19.25 | 15.83 | 17.43 | 21.00 | 20.41 | 15.23 |
Solved! Go to Solution.
Hi @Rez,
If the date columns in first table and second table are both date type, you can create a calculated column in first table with below DAX formula:
Billable days = LOOKUPVALUE ( TB2[Billable Days], TB2[Country of Origin], TB1[CountryOfOrigin], TB2[Date].[Year], TB1[DateWorked].[Year], TB2[Date].[MonthNo], TB1[DateWorked].[MonthNo] )
However, if the date column in second table is formatted as text, please create below calculated column first in TB1.
Year-month = FORMAT ( TB1[DateWorked], "MMM" ) & "-" & FORMAT ( TB1[DateWorked], "YY" )
Then, the lookupvalue should be modified as:
Billable days = LOOKUPVALUE ( TB2[Billable Days], TB2[Country of Origin], TB1[CountryOfOrigin], TB2[Date], TB1[Year-month] )
Best regards,
Yuliana Gu
Hi @Rez,
If the date columns in first table and second table are both date type, you can create a calculated column in first table with below DAX formula:
Billable days = LOOKUPVALUE ( TB2[Billable Days], TB2[Country of Origin], TB1[CountryOfOrigin], TB2[Date].[Year], TB1[DateWorked].[Year], TB2[Date].[MonthNo], TB1[DateWorked].[MonthNo] )
However, if the date column in second table is formatted as text, please create below calculated column first in TB1.
Year-month = FORMAT ( TB1[DateWorked], "MMM" ) & "-" & FORMAT ( TB1[DateWorked], "YY" )
Then, the lookupvalue should be modified as:
Billable days = LOOKUPVALUE ( TB2[Billable Days], TB2[Country of Origin], TB1[CountryOfOrigin], TB2[Date], TB1[Year-month] )
Best regards,
Yuliana Gu
Thanks for your help. Using your guidance, I was able to work it out.
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |