Creating team specific reports in Power BI

[RichardLBHF1]

Hi, 

I am looking to create a report that allows a team leader or department head look at there specific areas statistics between a date range. It needs to account for the fact that people will have started and finished within the area, they may of also come back to the area. 

I have a stats table, a date table, a name table and a team table. 

Stat table has columns for Name, date and various stats columns

Date Table has date, time , qtr, year columns

Name Table, has everyones name

Team Table has Name start date and end date.

What I want to do is agregate the statistics for the team between two dates. Ensuring that I only have the stats for the person for the time that they were in that specific team. i.e. if they satrted on the 1/1/1883 and finished 30/9/1899, if I was pulling a report for 20/9/1882 to 1/5/1888, I would only get the statiscs pulled through for the period 1/1/1883 to 1/5/1888. There could be other types of combinations but I am sure you see the situation I am trying to get to.

Ideally I want this to be able to be used in coinjunction to any slicer setting.

Thank You in advance for your support

[DataNinja777 · Accepted solution]

Hi @RichardLBHF1 ,

You can achieve the desired output by using a disconnected calendar table and writing a DAX formula like the one below to calculate headcount over different time periods for employees in a particular team.

Headcount =
SUMX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[End] >= [SelectedDate],
        1,
        BLANK ()
    )
)

The key to creating a dynamic headcount analysis is to use a disconnected calendar table. I've attached a link to an article that discusses this topic.

https://community.fabric.microsoft.com/t5/Community-Blog/Dynamic-Headcount-Analysis-using-Dax/ba-p/4...

I've also attached an example pbix file.

Best regards,