Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
data_guy_87
Frequent Visitor

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

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!

 

data_guy_87_1-1751563018347.png

 

 

 

 

12 REPLIES 12
v-aatheeque
Community Support
Community Support

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.

 

data_guy_87_0-1753377805397.png

 

 

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

DATEWORK_DAY
7/4/2025FALSE
7/3/2025TRUE
7/2/2025TRUE
7/1/2025TRUE
6/30/2025TRUE
6/29/2025FALSE
6/28/2025FALSE
6/27/2025TRUE

 

User Activity Table

ADJ_SIGNON_TIMESTAMP_DATEADJ_SIGNON_TIME_STAMPADJ_SIGNOFF_TIME_STAMPELAPSED_HOURSAPTWORK_AREA
7/2/20257/2/2025 5:077/2/2025 6:571.838APT10AL Station 1
7/2/20257/2/2025 5:157/2/2025 5:340.32APT8AL Station 3
7/2/20257/2/2025 5:347/2/2025 9:013.445APT8AL Station 3
7/2/20257/2/2025 6:497/2/2025 8:532.068APT8AL Station 3
7/2/20257/2/2025 7:077/2/2025 7:330.438APT9AL Station 2
7/2/20257/2/2025 7:417/2/2025 7:420.019APT10AL Station 1
7/2/20257/2/2025 8:067/2/2025 13:285.366APT9AL Station 2
7/2/20257/2/2025 8:067/2/2025 13:285.364APT9AL Station 2
7/2/20257/2/2025 8:067/2/2025 8:550.812APT10AL Station 1
7/2/20257/2/2025 8:067/2/2025 11:303.392APT9AL Station 2
7/2/20257/2/2025 8:067/2/2025 8:570.851APT10AL Station 1
7/2/20257/2/2025 8:077/2/2025 8:180.191APT9AL Station 2
7/2/20257/2/2025 8:077/2/2025 9:000.879APT10AL Station 1
7/2/20257/2/2025 8:077/2/2025 8:580.844APT10AL Station 1
7/2/20257/2/2025 8:087/2/2025 13:285.33APT8AL Station 3
7/1/20257/1/2025 5:127/1/2025 7:001.798APT8AL Station 3
7/1/20257/1/2025 7:007/1/2025 9:452.751APT8AL Station 3
7/1/20257/1/2025 7:087/1/2025 8:571.823APT10AL Station 1
7/1/20257/1/2025 7:107/1/2025 13:025.874APT8AL Station 3
7/1/20257/1/2025 7:107/1/2025 8:571.778APT10AL Station 1
7/1/20257/1/2025 7:127/1/2025 8:050.878APT10AL Station 1
7/1/20257/1/2025 7:137/1/2025 8:591.779APT10AL Station 1
7/1/20257/1/2025 7:137/1/2025 7:570.738APT8AL Station 3
7/1/20257/1/2025 7:137/1/2025 15:328.312APT8AL Station 3
7/1/20257/1/2025 7:147/1/2025 12:405.436APT8AL Station 3
7/1/20257/1/2025 7:157/1/2025 13:246.155APT8AL Station 3
7/1/20257/1/2025 7:177/1/2025 12:405.383APT8AL Station 3

6/30/2025

6/30/2025 4:116/30/2025 7:393.468APT8AL Station 3
6/30/20256/30/2025 5:366/30/2025 15:269.826APT8AL Station 3
6/30/20256/30/2025 7:116/30/2025 7:220.188APT10AL Station 1
6/30/20256/30/2025 7:116/30/2025 7:290.31APT10AL Station 1
6/30/20256/30/2025 7:126/30/2025 8:581.761APT10AL Station 1
6/30/20256/30/2025 7:156/30/2025 7:400.41APT9AL Station 2
6/30/20256/30/2025 7:156/30/2025 9:592.732APT9AL Station 2
6/30/20256/30/2025 7:156/30/2025 11:304.247APT8AL Station 3
6/30/20256/30/2025 7:166/30/2025 15:318.251APT8AL Station 3
6/30/20256/30/2025 7:206/30/2025 15:348.235APT8AL Station 3
6/30/20256/30/2025 7:216/30/2025 15:338.204APT8AL Station 3
6/30/20256/30/2025 7:226/30/2025 15:328.171APT8AL Station 3
6/30/20256/30/2025 7:226/30/2025 8:231.024APT8AL Station 3
6/30/20256/30/2025 7:246/30/2025 15:328.135APT8AL Station 3
6/30/20256/30/2025 7:246/30/2025 11:233.975APT9AL Station 2
6/30/20256/30/2025 7:296/30/2025 8:230.901APT8AL Station 3
6/30/20256/30/2025 7:406/30/2025 7:400.004APT9

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 :

vaatheeque_0-1753690028086.png

 

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!

 

 

FBergamaschi
Solution Sage
Solution Sage

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. 

data_guy_87_0-1752184157009.png

 

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_DATEADJ_SIGNON_TIME_STAMPADJ_SIGNOFF_TIME_STAMPELAPSED_HOURSWORK_DAY
4/11/20254/11/2025 7:194/11/2025 9:202.011TRUE
4/11/20254/11/2025 9:354/11/2025 9:550.336TRUE
4/11/20254/11/2025 10:004/11/2025 10:320.542TRUE
4/11/20254/11/2025 11:214/11/2025 11:210.003TRUE
4/11/20254/11/2025 11:214/11/2025 11:210.003TRUE
4/11/20254/11/2025 14:214/11/2025 14:290.144TRUE
4/11/20254/11/2025 14:314/11/2025 14:320.015TRUE
4/9/20254/9/2025 12:334/9/2025 14:231.834TRUE

 

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!

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.