Total people out of office by day

[mussol_]

Hello all,

my concern is the following:

I have one table with Personal ID and start and end dates every ID is out of office. 

Also I have a master date table.

The goal is to know how many people is out of office by each date of my master date table, for example, if there are five persons out of the office by 20th of august 2021, the row on the new column on the calendar table should show 5.

I will appreciate a lot your suggestions.

Thanks a lot in advance!.

[MAwwad · Accepted solution]

here's how you can do it in Power BI:

1. Import both tables into Power BI as separate queries.

2. Create a relationship between the two tables based on the "Date" column.

3. Go to the "Modeling" tab and create a new calculated table with the following DAX formula:

   OutOfOffice = ADDCOLUMNS ( CROSSJOIN ( 'DimDate', 'Out of Office' ), "Count", CALCULATE ( COUNTROWS ( 'Out of Office' ), FILTER ( 'Out of Office', 'Out of Office'[Start Date] <= 'DimDate'[Date] && 'Out of Office'[End Date] >= 'DimDate'[Date] ) ) )

   This formula creates a new table called "OutOfOffice" with a row for each combination of date and personal ID in the "Out of Office" table. The "Count" column calculates the number of people out of office for each date and ID combination.

4. Create a new visual in your report using the "Date" column from the "DimDate" table and the "Count" column from the "OutOfOffice" table.

5. Customize the visual as needed (e.g. change the visual type to a line chart or bar chart).

That's it! You should now have a visual showing the total number of people out of office for each date in your master date table.