"What is the percentage of my time that I spend at work?". I posed this question to myself a few days ago. This article shows you how I approached this question and how I solved the challenge.

Introduction
A few days ago, I asked myself: What is the Percentage of my time at work?
Being a Power BI aficionado, I wanted to answer this question with Power BI.
This piece is like a Log, in which you can follow how I approached this question and how I solved it.
Questions to the data
First of all, what are precisely the questions?
Daily, I want to know the Percentage of work hours related to the 24 hours of the day.
Monthly, I want to know the average Percentage related to the hours of all days of each month. In addition, I want to calculate the relation of the sum of the work hours vs the sum of the hours per month.
I want to know the rate of my total working time vs the billable time on both levels.
As I have data only for the actual year, I don’t have to calculate anything at the year level.
I have two sets of data:
- The list of all billable hours by customer
- The list of work-hours without the distinction if billable or not
Prepare the Data Model
I loaded the two data sets into Power BI and added a Date table.
As I need to perform analysis on Time Data, I need a Time table.
I created a Time table with the following Attributes:
-
Time_Key A Key column with the format hhmmss, without leading zeroes
-
Time The time column
-
Hour The hour of the current time
-
Minute The minute of the current time
-
MilitaryTime The time in the format hhmm with leading zeroes
-
Hour_Dec The hour with the minute as a decimal
-
Hour_Frag The fraction of the hour per minute
-
Day_Frag The fraction of the day per minute
Here is an extract of the Time table:

The exact formulas on how I have calculated these number is beyond the scope of this article. Please leave a comment If you want to know the details, and I will write a description on this topic.
Tweaking the Data Model
I have to tweak my Data model:

Look at the Relationship between the tables Time_Min and Rapport 2021 (=Reporting 2021).
Because I want to use the Time_Min table as a Dimension- and as a Fact-table, I have to set the Relationship to Bidirectional-Filtering.
I suspect that I have to do the same with the other tables, as I want to combine the data in the tables "Rapport 2021" and "Soll-Ist" (= Target vs effective hours).
Note: The table- and column names are in German as my sources deliver data with german wording. I will do my best to translate everything into English
Simple Average
The first step is to see what is the result of a simple Average of My data.
As you can see in the following Screen-Shot, the result looks far from correct:

The Average will calculate the Average over all rows, regardless of the Granularity. The Average seems to produce the wrong results, but it points me to the approach I will follow for the next steps.
It makes no sense to calculate an average over several days. I need to get the number of days and divide it by the number of hours.
But, for one single day, the Average is correct.
Handling of different calculations
I need to evaluate the calculation level (Month or Day) and perform different calculations for these two levels.
I’ll do it with the following intermediary Measure:
Calendar Check =
SWITCH(TRUE()
, HASONEVALUE('Date'[DateKey]), "Date"
, HASONEVALUE('Date'[MonthKey]), "Month"
, "Others"
)
The column MonthKey identifies each month for each year. For example, June 2021 is 202106
Now, I can use the result of this Measure in all other Measures and decide which calculation is the correct one.
Calculation the Part of Life Time
First, I want to calculate my work time’s rate vs my lifetime at the monthly level.
For example:
- The day has 24 hours
- I work for 8 hours in one day
- The part of work time is 8 / 24 = 33.3%
When I’m at the monthly level, I cannot use the Time_Min table, as it goes only up to 24 hours. But, I work more than 24 hours in one month.
So, I need to perform the following calculation:
Work Part per Month =
VAR SumOfHours = SUM('Rapport 2021'[Wertschöpfende Menge])
VAR HoursPerMonth = COUNTROWS('Date') * 24
VAR WorkPartPerMonth = DIVIDE(SumOfHours , HoursPerMonth)
RETURN
WorkPartPerMonth
The Variable SumOfHours contains the sum of billable hours (= Wertschöpfende Menge).
The Variable HoursPerMonth counts the days in the actual period and multiplies it by 24 (hours).
To Consider only the Workdays, without the weekends, I used this for HoursPerMonth:
CALCULATE(COUNTROWS('Date')
,'Date'[IsWorkday] = TRUE()) * 24
The result of the calculation is stored in the Variable WorkPartPerMonth through a Division of SumOfHours by HoursPerMonth.
Second, I want to calculate the same rate at the daily level.
My data model allows me to summarize the actual working hours and the Day_Frag amount, as both are for one day and cannot exceed 24 hours.
Now I can calculate the sum of Day_Frag to get the correct result.
As the Filter context selects the matching rows in the Time_Min table for each day, I can calculate a Sum of Day_Frag to get the correct result.
Here is the Measure:
Part of Lifetime =
SWITCH(TRUE()
,[Calendar Check] = "Date"
,SUM('Time_Min'[Day_Frag])
,[Calendar Check] = "Month"
,[Work Part per Month]
,BLANK()
)
The Measure [Work Part per Month] works and any other level as well. But, as my data covers only 2021, I consider only the daily and monthly levels.
Calculate relation between working and billable hours
Now I have to solve the last question: I want to know the rate of my total working time vs the billable time on both levels.
The Table Soll-Ist contains a list of hours per day in two columns:
- Soll: This is the number of hours I have to work per day (Target work hours)
- Ist: This is the number of hours I worked for a specific day (Effective work hours)
I started to think about how I can calculate this Measure.
My first thought was to use a LOOKUPVALUE() to get the Value from the Soll-Ist table according to the date in the Rapport 2021 table.
As explained in my other Article, Don’t start with optimized code. It may not be a good idea; I like to have simple and elegant solutions. I don’t consider LOOKUPVALUE to be a simple solution.
The second approach was to set the relationships to Bidirectional-Filtering. I don’t want to go this way, as this approach can have unwanted side effects,
In the end, I decided to add one more Relationship between the two tables:

Then, I created a New Measure to divide the two numbers:
Billable time vs Worktime = DIVIDE(
SUM('Rapport 2021'[Wertschöpfende Menge])
, SUM('Soll-Ist'[Ist Stunden])
)
At first, I was surprised that it worked:

Then I realized that the Filter Context on both tables was set through the Date table. So I don’t need to do anything more to get the correct result.
Conclusion
I wrote this article during the development of the Report.
I expected more complex code in this example as time calculation can be tricky. Fortunately, this didn’t happen.
I think that this is due to a good data model.
It always makes sense to spend time thinking about a good data model to simplify your Dax code.
The Time_Min table has helped me calculate the results, even though I didn’t use all the columns in this table.
But the most significant benefit I had, was to clearly define the requirements and which question I wanted to answer. With the clarified requirements, I concentrated on what I need to do and not on what I liked to do.
Again, the time to clarify the requirements is worth it.
Here is the result:

As you can see, I spend between 17 % and 31 % of my time at work, and 75% of my work time is billable, which is not bad considering that I was on holiday during January and April and I had a three-day Training during April.
Unfortunately, I lost a customer at the start of July. Nevertheless, I still have 76.4% of billable work time.
I think I can be happy with these results.

If you appreciate my work, feel free to support me through
Or scan this QR Code:

Any support is greatly appreciated.
Thank you.