cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measurement to calculate time in office per person

Hello Power BI Community 😊

I am putting together a report to show the percentage of time a person spends working in office compared to working from home.

My table includes the persons name, date and the status; In Office (IO), Working From Home (WFH), Out of Office (OOO).

The report can be filtered by date hierarchy and by the names of each person.

I plan to use the Card Visual to show the percentage of IO and WFH, the total that should not include dates spent OOO.

What measurment (other other method) would be most efficient for this?

Here's an example of the table:

 Name Weekday Date Status James Monday 18/10/2022 OOO James Tuesday 19/10/2022 IO James Wednesday 20/10/2022 IO James Thursday 21/10/2022 IO James Friday 22/10/2022 WFH Natasha Monday 18/10/2022 WFH Natasha Tuesday 19/10/2022 WFH Natasha Wednesday 20/10/2022 IO Natasha Thursday 21/10/2022 IO Natasha Friday 22/10/2022 IO Evelyn Monday 18/10/2022 OOO Evelyn Tuesday 19/10/2022 IO Evelyn Wednesday 20/10/2022 IO Evelyn Thursday 21/10/2022 IO Evelyn Friday 22/10/2022 IO

1 ACCEPTED SOLUTION
Resolver III

Hi @Natasha93,

You need to create a new measure with the below DAX

IO =
Var allWorkingDays = CALCULATE(Count(TableName[Date]), Status <> "OOO")
Var inOffice = CALCULATE(Count(TableName[Date]), Status = "IO")
RETURN
DIVIDE(inOffice, allWorkingDays)

After you create this measure, change the type of it to Percentage

You can create the same formula for the WFH

5 REPLIES 5
Resolver III

You need to add the below filter in BOLD

OOI_percent =

VAR _IOcount = CALCULATE( COUNT( [name] ),FILTER( [YourTableName] , [Status] = "IO"))
VAR _Total = CALCULATE( COUNT( [name] ), Status <> "OOO")

RETURN
DIVIDE(_IOcount,_Total,0)

Resolver III

Hi @Natasha93,

You need to create a new measure with the below DAX

IO =
Var allWorkingDays = CALCULATE(Count(TableName[Date]), Status <> "OOO")
Var inOffice = CALCULATE(Count(TableName[Date]), Status = "IO")
RETURN
DIVIDE(inOffice, allWorkingDays)

After you create this measure, change the type of it to Percentage

You can create the same formula for the WFH

Frequent Visitor

Hi there,

Assuming the date values represent whole working days, a simple count-based % measure should be sufficent.

Using IO as an example:

OOI_percent =

VAR _IOcount = CALCULATE( COUNT( [name] ),FILTER( [YourTableName] , [Status] = "IO"))
VAR _Total = CALCULATE( COUNT( [name] ) )

RETURN
DIVIDE(_IOcount,_Total,0)

Do the same for WFH too and you should have your values. To note, everything in the [] will need adjusting to reflect the actual table and column names.

Frequent Visitor

Hi @thomthom_uk ,

That worked! Thanks!

However, my only issue is that the In Office and Work From Home measurement also include Out of Office in the grand total.

If someone works in office on a Monday and Tuesday, but have booked Wed-Fri as holiday, techincally they've spent 100% of their working time at the office.

Is there an adjustment that can be made to not include dates marked "OOO" in these measurements?

Frequent Visitor

For exclusions, replace your total calculation with the following:

VAR _Total = CALCULATE( COUNT( [name] ),FILTER( [YourTableName] , [Status] <> "OOO"))

In general, adjust the filters to reflect what you need. You might need to add a note to report that the IO and WFH excludes OOO times, in case someone comments about the % values not adding up to 100%

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors