The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone! I need to build a measure that counts all workdays (workdays calculated with MIN(clock in date) and MAX(clock in date) where the sum of [elapsed_hours] is under 8. I have a measure built, but the count does not include work days where no clock ins happened at all. Here is my current measure with a bit of the data I'm working with... as you can see from the raw data, my count should be 3 instead of 2 I am getting currently (current measure isn't capturing April 10th, which is a workday with no clock ins... we want these types of days included in the count). Any help is greatly appreciated!
Hi @data_guy_87
Thanks for reaching out to Fabric Community Forum.
To show all continuous dates in the Power BI table visual, even if no activity is logged, first create a Calendar table using the CALENDER function with the minimum and maximum dates from your data.
Then, create a relationship between the Calendar table and your fact table on the date column. In the visual, use the Date field from the Calendar table and add the other fields from the fact table. Finally, enable the “Show items with no data” option on the Date field in the visual.
For better understanding i am sharing the PBIX file.
Hope this helps!!
If this post was helpful, consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
Hi @data_guy_87
I wanted to follow up to see if the suggestion shared earlier helped you build the measure to correctly count all workdays including those with no clock-ins based on your Calendar table and the “Show items with no data” option.
As mentioned, by creating a Calendar table using the CALENDAR function (spanning from MIN to MAX clock-in dates) and linking it to your fact table, then using the Date field from the Calendar table in your visual, you can ensure all workdays appear, even those without activity. Your measure can then evaluate elapsed hours per day and include days under 8 hours or with no data.
If you're still facing challenges or have any follow-up questions, please feel free to let us know. We're here to help!
Looking forward to your update!!
Thank you for being an active member of the Microsoft Fabric Community!
Hi @v-aatheeque
I have added my calendar table and my fact table (user clocking activity) below. They have a one-many relationship on the date. I also shared a matrix visual I am trying to build, filtering on the APT and WORK_AREA columns in the fact table.
MEASURE NEEDED: Lost Days - I need to only look at dates between the first clock in, and the last clock in for a specific APT/WORK_AREA combination. I then need to count all dates in the range that have a total ELAPSED_HOURS < 8 and where WORK_DAY = "TRUE". The measure must count true working days within the range that might not have any clock in records.
Hope this info helps.
Sample of both tables below:
Calendar Table
DATE | WORK_DAY |
7/4/2025 | FALSE |
7/3/2025 | TRUE |
7/2/2025 | TRUE |
7/1/2025 | TRUE |
6/30/2025 | TRUE |
6/29/2025 | FALSE |
6/28/2025 | FALSE |
6/27/2025 | TRUE |
User Activity Table
ADJ_SIGNON_TIMESTAMP_DATE | ADJ_SIGNON_TIME_STAMP | ADJ_SIGNOFF_TIME_STAMP | ELAPSED_HOURS | APT | WORK_AREA |
7/2/2025 | 7/2/2025 5:07 | 7/2/2025 6:57 | 1.838 | APT10 | AL Station 1 |
7/2/2025 | 7/2/2025 5:15 | 7/2/2025 5:34 | 0.32 | APT8 | AL Station 3 |
7/2/2025 | 7/2/2025 5:34 | 7/2/2025 9:01 | 3.445 | APT8 | AL Station 3 |
7/2/2025 | 7/2/2025 6:49 | 7/2/2025 8:53 | 2.068 | APT8 | AL Station 3 |
7/2/2025 | 7/2/2025 7:07 | 7/2/2025 7:33 | 0.438 | APT9 | AL Station 2 |
7/2/2025 | 7/2/2025 7:41 | 7/2/2025 7:42 | 0.019 | APT10 | AL Station 1 |
7/2/2025 | 7/2/2025 8:06 | 7/2/2025 13:28 | 5.366 | APT9 | AL Station 2 |
7/2/2025 | 7/2/2025 8:06 | 7/2/2025 13:28 | 5.364 | APT9 | AL Station 2 |
7/2/2025 | 7/2/2025 8:06 | 7/2/2025 8:55 | 0.812 | APT10 | AL Station 1 |
7/2/2025 | 7/2/2025 8:06 | 7/2/2025 11:30 | 3.392 | APT9 | AL Station 2 |
7/2/2025 | 7/2/2025 8:06 | 7/2/2025 8:57 | 0.851 | APT10 | AL Station 1 |
7/2/2025 | 7/2/2025 8:07 | 7/2/2025 8:18 | 0.191 | APT9 | AL Station 2 |
7/2/2025 | 7/2/2025 8:07 | 7/2/2025 9:00 | 0.879 | APT10 | AL Station 1 |
7/2/2025 | 7/2/2025 8:07 | 7/2/2025 8:58 | 0.844 | APT10 | AL Station 1 |
7/2/2025 | 7/2/2025 8:08 | 7/2/2025 13:28 | 5.33 | APT8 | AL Station 3 |
7/1/2025 | 7/1/2025 5:12 | 7/1/2025 7:00 | 1.798 | APT8 | AL Station 3 |
7/1/2025 | 7/1/2025 7:00 | 7/1/2025 9:45 | 2.751 | APT8 | AL Station 3 |
7/1/2025 | 7/1/2025 7:08 | 7/1/2025 8:57 | 1.823 | APT10 | AL Station 1 |
7/1/2025 | 7/1/2025 7:10 | 7/1/2025 13:02 | 5.874 | APT8 | AL Station 3 |
7/1/2025 | 7/1/2025 7:10 | 7/1/2025 8:57 | 1.778 | APT10 | AL Station 1 |
7/1/2025 | 7/1/2025 7:12 | 7/1/2025 8:05 | 0.878 | APT10 | AL Station 1 |
7/1/2025 | 7/1/2025 7:13 | 7/1/2025 8:59 | 1.779 | APT10 | AL Station 1 |
7/1/2025 | 7/1/2025 7:13 | 7/1/2025 7:57 | 0.738 | APT8 | AL Station 3 |
7/1/2025 | 7/1/2025 7:13 | 7/1/2025 15:32 | 8.312 | APT8 | AL Station 3 |
7/1/2025 | 7/1/2025 7:14 | 7/1/2025 12:40 | 5.436 | APT8 | AL Station 3 |
7/1/2025 | 7/1/2025 7:15 | 7/1/2025 13:24 | 6.155 | APT8 | AL Station 3 |
7/1/2025 | 7/1/2025 7:17 | 7/1/2025 12:40 | 5.383 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 4:11 | 6/30/2025 7:39 | 3.468 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 5:36 | 6/30/2025 15:26 | 9.826 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:11 | 6/30/2025 7:22 | 0.188 | APT10 | AL Station 1 |
6/30/2025 | 6/30/2025 7:11 | 6/30/2025 7:29 | 0.31 | APT10 | AL Station 1 |
6/30/2025 | 6/30/2025 7:12 | 6/30/2025 8:58 | 1.761 | APT10 | AL Station 1 |
6/30/2025 | 6/30/2025 7:15 | 6/30/2025 7:40 | 0.41 | APT9 | AL Station 2 |
6/30/2025 | 6/30/2025 7:15 | 6/30/2025 9:59 | 2.732 | APT9 | AL Station 2 |
6/30/2025 | 6/30/2025 7:15 | 6/30/2025 11:30 | 4.247 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:16 | 6/30/2025 15:31 | 8.251 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:20 | 6/30/2025 15:34 | 8.235 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:21 | 6/30/2025 15:33 | 8.204 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:22 | 6/30/2025 15:32 | 8.171 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:22 | 6/30/2025 8:23 | 1.024 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:24 | 6/30/2025 15:32 | 8.135 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:24 | 6/30/2025 11:23 | 3.975 | APT9 | AL Station 2 |
6/30/2025 | 6/30/2025 7:29 | 6/30/2025 8:23 | 0.901 | APT8 | AL Station 3 |
6/30/2025 | 6/30/2025 7:40 | 6/30/2025 7:40 | 0.004 | APT9 | AL Station 2 |
Hi @data_guy_87
Based on the data you provided, we created a DAX measure that calculates 'Lost Days' by evaluating working days between the first and last clock-in for each APT and WORK_AREA.
It checks whether ELAPSED_HOURS < 8 or the date has no clock-in. The Matrix visual in Power BI effectively shows this breakdown across stations, with both Lost Days and total working days for full comparison.
Lost Days =
VAR SelectedAPT = SELECTEDVALUE('UserActivity'[APT])
VAR SelectedWorkArea = SELECTEDVALUE('UserActivity'[WORK_AREA])
VAR MinDate =
CALCULATE(
MIN('UserActivity'[ADJ_SIGNON_TIMESTAMP_DATE]),
'UserActivity'[APT] = SelectedAPT,
'UserActivity'[WORK_AREA] = SelectedWorkArea
)
VAR MaxDate =
CALCULATE(
MAX('UserActivity'[ADJ_SIGNON_TIMESTAMP_DATE]),
'UserActivity'[APT] = SelectedAPT,
'UserActivity'[WORK_AREA] = SelectedWorkArea
)
VAR WorkingDaysInRange =
FILTER(
'calender',
'Calender'[DATE] >= MinDate &&
'Calender'[DATE] <= MaxDate &&
'Calender'[WORK_DAY] = TRUE()
)
RETURN
COUNTROWS(
FILTER(
WorkingDaysInRange,
VAR ThisDate = 'Calender'[DATE]
VAR DailyHours =
CALCULATE(
SUM('UserActivity'[ELAPSED_HOURS]),
'UserActivity'[APT] = SelectedAPT,
'UserActivity'[WORK_AREA] = SelectedWorkArea,
'UserActivity'[ADJ_SIGNON_TIMESTAMP_DATE] = ThisDate
)
RETURN
ISBLANK(DailyHours) || DailyHours < 8
)
)
Expected Output :
Hope this helps !!
Thanks...
Hi @data_guy_87
Just following up on the solution we shared earlier regarding the calculation of ‘Lost Days’ based on your sample data.
We used your provided dataset to build a DAX measure that calculates working days between the first and last clock-in per APT and WORK_AREA. It flags a day as "lost" if Elapsed_hours < 8 or there was no clock-in for that date.
The matrix visual should now correctly show both Lost Days and total Work Days for clear comparison across stations.
If the current setup addresses your scenario, please let us know otherwise, feel free to share any updates or additional data so we can support you further.
Looking forward to your response!
Hi @data_guy_87
Just checking in on the solution we provided for calculating 'Lost Days' using your sample data.
We created a DAX measure that identifies working days between the first and last clock-in for each APT and WORK_AREA. A day is marked as "lost" if there’s no clock-in or if Elapsed hours is less than 8.
The matrix visual should now display both Lost Days and total Work Days, making it easier to compare across stations.
If this meets your needs, great! Otherwise, feel free to share updated info or datawe’re happy to help further.
If we don’t hear back, we may close this thread in line with our community guidelines, but you’re always welcome to post a new query anytime.
Looking forward to hearing from you!
Thank you for being part of the Microsoft Fabric Community!
Hi data_guy_87,
I am not sure I got your question. Please help me understand and provide the answers to the below questions
1 - where should I see the 3 instead of the 2? In the image you sent, the measure result is not shown
2 - does the work day column in the picture come from the calendar table?
3 - can you provide the sample data in the picture in a pasted format that I can copy and paste in Power BI and solve?
PS you should not create calculated columns with SUMMARIZE, it is very dangerous, I shall offer you a revised DAX code in that respect, plus solve the problem possibly (use ADDCOLUMNS on top of SUMMARIZE)
Best
@FBergamaschi additional info:
1 - Here is a snapshot of the visual with the measure (LostDays). The bottom row of the snapshot reflects the data table from my original post.
2 - The WORK_DAY column is indeed coming from a related calendar table. (It is only 'FALSE' on weekends (SAT/SUN) and holidays.
3 - Here is the data:
ADJ_SIGNON_TIMESTAMP_DATE | ADJ_SIGNON_TIME_STAMP | ADJ_SIGNOFF_TIME_STAMP | ELAPSED_HOURS | WORK_DAY |
4/11/2025 | 4/11/2025 7:19 | 4/11/2025 9:20 | 2.011 | TRUE |
4/11/2025 | 4/11/2025 9:35 | 4/11/2025 9:55 | 0.336 | TRUE |
4/11/2025 | 4/11/2025 10:00 | 4/11/2025 10:32 | 0.542 | TRUE |
4/11/2025 | 4/11/2025 11:21 | 4/11/2025 11:21 | 0.003 | TRUE |
4/11/2025 | 4/11/2025 11:21 | 4/11/2025 11:21 | 0.003 | TRUE |
4/11/2025 | 4/11/2025 14:21 | 4/11/2025 14:29 | 0.144 | TRUE |
4/11/2025 | 4/11/2025 14:31 | 4/11/2025 14:32 | 0.015 | TRUE |
4/9/2025 | 4/9/2025 12:33 | 4/9/2025 14:23 | 1.834 | TRUE |
Not enough sample data. Please provide sample data that covers your issue or question completely, including all scenarios.
Note that DAX has a NETWORKDAYS function where you can directly feed your weekend and holiday details if you want.
Hi @data_guy_87
Based on the suggest can you provide the sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Hi @data_guy_87
I wanted to follow up on my earlier message regarding the sample data for the visual you shared with the LostDays measure. You had mentioned that the WORK_DAY column comes from a related calendar table and is marked FALSE for weekends and holidays.
If you're able to share a small sample of the data or provide any further details, I'd be happy to help troubleshoot the issue more effectively.
Looking forward to hearing from you!
To report on things that are not there you need to use disconnected tables and/or crossjoins
In your case that means you need a disconnected calendar table.
User | Count |
---|---|
26 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
30 | |
15 | |
12 | |
12 | |
7 |