help with average between two periods for employees absent on a portion of the calculated period

[rdops]

Hi all,

I am in a bit of a picke and hoping you can help.

I want to calculate the % average of employee utilisation for a given period of time. I managed to get that, although I am sure there is a smarter way, but anyway, this is the data I have, what I did and what I need:

1. I have a master document with employees names and the percentage they were utilised every month from July 2019 to date

2. I created a measure that counts the total months in 2019 (which gives me the average for this year by summing all months from july to december divied by 6 (July to Dec). Did the same for 2020.

3. Then I created a measure that sums all months from July 2019 to July 2020, that gives me the average for the period.

4. Now to the problem: Some people were not in the company in 2019, so when I use the 'to date' calculation from July 2019, the average for those who were not emplyeed drop since the formula calculates against the entire period (July-July). How do I fix this?

Thanks in advance,

RD

[amitchandak]

@rdops , You need to distinct count of the month that will make sure the average is correct. To make sure it is correct, tak  month year from the table where employee monthly data is there.

[rdops]

see the formula I used and the results I get. But I am not sure how to differenciate them by year and also not sure how to use this count for months in the formula.

[amitchandak]

@rdops , something like this.

averageX(summarize(table, Table[employee], "_1", distinctCOUNT(Table[Month Year]),"_2",sum(Table[Chargeability])),divide([_2],[_1]))

Here month-year is in a format like jan-2020

But will this work depend on output you need.

Can you share sample data and sample output in a table format?

[rdops]

Employee	Chargeability %	Month
Mary	40	Jul-19
Mary	50	Aug-19
Mary	50	Sep-19
Mary	50	Oct-19
Mary	50	Nov-19
Mary	30	Dec-19
Mary	50	Jan-20
Mary	50	Feb-20
Mary	40	Mar-20
Mary	50	Apr-20
Mary	40	May-20
Mary	30	Jun-20
Mary	30	Jul-20
Peter	50	Jul-19
Peter	50	Aug-19
Peter	20	Sep-19
Peter	40	Oct-19
Peter	30	Nov-19
Peter	30	Dec-19
Peter	60	Jan-20
Peter	40	Feb-20
Peter	30	Mar-20
Peter	30	Apr-20
Peter	50	May-20
Peter	20	Jun-20
Peter	30	Jul-20
John	15	Mar-20
John	40	Apr-20
John	50	May-20
John	30	Jun-20
John	20	Jul-20

This is how the data is sort of structured.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[rdops]

Hi Ashish, thank you so, so much for this. It worked. Amazing!!!

Really appreciate your time. I wish I could understand the logics behind this, but I am just happy I got a solution.

Super grateful!

RD

[Ashish_Mathur]

You are welcome.  Glad my solution worked.

[rdops]

Hi Amit, not sure I understood this solution, do you mind giving me an example? I tried distinct count and it came back 1 for each month (and 2 for July as it was counting July twice (for 2019 and 2020) but then how will this calculate the total including all months? What formula cna I use for this?

[amitchandak]

@rdops , if you do the distinct count of month year(format you have given). Then it takes it correctly. Can you share sample data and sample output in table format?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[rdops]

Hi Ashish, thanks for your reply, so the data is structred roughtly as above. As you can see, the calculation will be correct for Peter and Mary because they both have been in the business for all of 2019 and all of 2020 so far. But it will be incorrect for John, because John started with us in March, so his 2020 average is calculating assuming a count of 6 months, when he has been in the business for 5 months, and his Year to year will be even more incorrect because will include all months of 2019, when he was not in the company. I hope it makes sense?

[Ashish_Mathur]

Hi,

So the answer for John should be 31.  Share the link from where i can download your PBI file.