Best way to model this data?

[vanessafvg]

I have a table that i merged that has the following information

employee no

start date

end date

From the above i need to calculate the following

1. No of employees at a point in time down to day level

2. no of leavers

3. no of Joiners

4. Average no of employees by day

5. staff turnover

 For these measures i currently have the following table from my original table

Daily Aggregate = SUMMARIZECOLUMNS('Date'[Year], 'Date'[Month], 'Date'[Month Number], 'Date'[Date], 'Date'[Days of Month], "No Of Employees", CALCULATE(DISTINCTCOUNT('Employees All Time'[Employee No.]),
filter(
all('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date])
)), "Leavers", CALCULATE(DISTINCTCOUNT('Employees All Time'[Employee No.]),
USERELATIONSHIP('Employees All Time'[Leaving Date],'Date'[Date])),
"Joiners" , CALCULATE(DISTINCTCOUNT('Employees All Time'[Employee No.]), USERELATIONSHIP('Employees All Time'[Start Date],'Date'[Date])))

and then created these off it it

Average Employee Count = CALCULATE(DIVIDE(sum('Daily Aggregate'[ Employees]),max('Date'[Days of Month])))

Staff Turnover = CALCULATE(divide(sum('Daily Aggregate'[Leavers]),[Average Employee Count]))

I was just wondering if this is the best way to do it?  The thing i struggled was creating the average, so i created a record for each day to get the average per month.  Just wondering if any one has a better way to do this.  Its also not very dynamic, for example if i wanted to get an average per week,  quarter etc...

[v-shex-msft]

Hi @vanessafvg,

>>Its also not very dynamic, for example if i wanted to get an average per week,  quarter etc...

Current power bi not support dynamic calculate the different date range.

In my opinion, I'd like to suggest write these formulas and use if statement to switch the displayed result.

Regards,

Xiaoxin Sheng