Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rez
Regular Visitor

Converting Mandays into FTE based on country and month

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.

 

 

ProjectLabourerCountryOfOriginDateWorkedMandays WorkedFTE?
1JamesSouth Africa01/01/2018200 
2KilnerSouth Africa01/09/20181000 
3MagnussonUkraine01/10/2018250 
4EdmondScotland01/03/2018350 
5KendrickSouth Africa01/03/2018800 
6JamesSouth Africa01/01/2018200 
7KilnerSouth Africa01/11/20181000 
8MagnussonUkraine01/11/2018250 
2EdmondScotland01/01/2018350 
3KendrickSouth Africa01/03/2018800 
11JamesSouth Africa01/05/2018200 
10KilnerSouth Africa01/01/20181000 
12MagnussonUkraine01/10/2018250 
4EdmondScotland01/08/2018350 
5KendrickSouth Africa01/01/2018800 
9JamesSouth Africa01/11/2018200 
9KilnerSouth Africa01/10/20181000 
9MagnussonUkraine01/11/2018250 
9EdmondScotland01/11/2018350 
10KendrickSouth Africa01/10/2018800 

 

DateCountry of OriginBillable Days
Jan-18South Africa16.00
Feb-18South Africa17.20
Mar-18South Africa19.00
Apr-18South Africa17.20
May-18South Africa19.20
Jun-18South Africa20.00
Jul-18South Africa18.00
Aug-18South Africa15.00
Sep-18South Africa17.00
Oct-18South Africa21.30
Nov-18South Africa20.60
Dec-18South Africa15.00
Jan-18Ukraine17.00
Feb-18Ukraine18.20
Mar-18Ukraine20.00
Apr-18Ukraine18.20
May-18Ukraine20.20
Jun-18Ukraine18.98
Jul-18Ukraine18.25
Aug-18Ukraine14.83
Sep-18Ukraine16.43
Oct-18Ukraine20.00
Nov-18Ukraine19.41
Dec-18Ukraine14.23
Jan-18Scotland18.56
Feb-18Scotland18.53
Mar-18Scotland17.73
Apr-18Scotland17.14
May-18Scotland16.43
Jun-18Scotland20.00
Jul-18Scotland19.25
Aug-18Scotland15.83
Sep-18Scotland17.43
Oct-18Scotland21.00
Nov-18Scotland20.41
Dec-18Scotland15.23
CountryJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-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
1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
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-yulgu-msft
Employee
Employee

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

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

Thanks for your help. Using your guidance, I was able to work it out.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.