Forum Discussion

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
9 years ago

Best way to model this data?

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...

 

 

 

 

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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