cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rez Frequent Visitor
Frequent 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

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: Converting Mandays into FTE based on country and month

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
Microsoft v-yulgu-msft
Microsoft

Re: Converting Mandays into FTE based on country and month

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

Rez Frequent Visitor
Frequent Visitor

Re: Converting Mandays into FTE based on country and month

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors