Measure needed to count all work days with less than 8 hours logged into.

[data_guy_87]

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!

[v-aatheeque]

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!

[v-aatheeque]

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!

[FBergamaschi]

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

[data_guy_87]

@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

[lbendlin]

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.

[v-aatheeque]

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

[lbendlin]

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.