cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Help With Missing Timesheet Counts

Hello everyone,

 

I need some help with a Missing Timesheet report that I'm doing for my company. Basically, I have 2 tables that have a 1-to-many relationship:

  • Employee List - table of employee info
  • Timesheet Hours - table of weekly timesheet information (hours worked, projects that were worked on, etc.)

 

So far, with the help of another user on here, I was able to get the "Missing" timesheets to show up using this measure:

Approval = VAR __status = CALCULATE ( LASTNONBLANK ('Timesheet Hours'[Approval Status], 1 ) )
RETURN IF ( __status == BLANK(), "Missing", __status )

 

Here is my end goal:

  • Create a missing timesheet report with these filters:
    • Timesheet Required = Yes
    • Time Period = [Insert any week]
  • Be able to view the total count of "Missing", "Approved", or "Pending" timesheets on any week that is filtered. 

 

I have created a mockup as to not use company data. Here's a picture of what I have so far:
MissingTimesheet - Test.PNG

 

I don't see where I can upload my actual PowerBI file, but I can upload the mockup file if needed if someone could tell me how. 

 

Thanks for any help you may be able to provide!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Help With Missing Timesheet Counts

Hi @DJLight890 ,

Please update the formula of measure "Missing count" as below:

Missing Count = 
VAR _selections =
    ALLSELECTED ( 'Timesheet Hours'[Period Name] )
VAR _countofPeriod =
    COUNTROWS ( _selections )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Employee List'[Employee Name] ),
        'Employee List'[Timesheet Required] = "Yes"
    ) * _countofPeriod
        - CALCULATE (
            COUNT ( 'Timesheet Hours'[Period Name] ),
            'Employee List'[Timesheet Required] = "Yes",
            FILTER (
                ALL ( 'Timesheet Hours' ),
                'Timesheet Hours'[Period Name] IN _selections
            )
        )

Missing count.JPG

 

 

Best Regards

Rena

View solution in original post

14 REPLIES 14
Highlighted
Helper I
Helper I

Re: Help With Missing Timesheet Counts

I don't understand, do you need to return the blank values?

if so.
use one if
example: standard sum calculation, if (calculation= blank (); 0; calculation)

😶

Highlighted
Helper I
Helper I

Re: Help With Missing Timesheet Counts

I apologize if I'm not explaining it well. So as seen in the picture in my original post, I like that I am now able to see the employees that are missing, pending, or approved. I'm trying to get to where I can see the count of those that are "Missing", "Approved", or "Pending" as seen in this screenshot I found (In progress timesheets would be equivalent to my pending):

 

Timesheet Count.PNG

 

Thanks!

Highlighted
Helper I
Helper I

Re: Help With Missing Timesheet Counts

So, if it's a calculation using a value as a filter,

try:

Measure = CALCULATE(COUNT(table[Column]),FILTER(table,[Approval]="Pending"))

Highlighted
Community Support
Community Support

Re: Help With Missing Timesheet Counts

Hi @DJLight890 ,

You can create a measure as below to get it:

Count of Missing = 
CALCULATE (
    DISTINCTCOUNT ( 'Timesheet Hours'[Employee Name] ),
    'Employee List'[Timesheet Required] = "Yes",
    FILTER (
        'Timesheet Hours',
        'Timesheet Hours'[Period Name]
            = SELECTEDVALUE ( 'Timesheet Hours'[Period Name] )
            && [Approval] = "Missing"
    )
)

Help With Missing Timesheet Counts.JPG

Best Regards

Rena

Highlighted
Helper I
Helper I

Re: Help With Missing Timesheet Counts

@yingyinr  Wow, thank you so much! That looks exactly like what I'm looking for!

 

I'm not sure what I'm doing wrong, but I copied everything exactly as you have it, including making sure my measures were in the same tables as yours. However, when I use a card for Count of Missing, I'm getting "Blank". Any thoughts?

 

Blank Showing.PNG

Highlighted
Helper I
Helper I

Re: Help With Missing Timesheet Counts

@marceloVVR  Thank you for your reply! I was able to get this to work too! I'm awaiting reply from the other person helping with this before I accept one of the replies as the solution. Thanks again! 

Highlighted
Community Support
Community Support

Re: Help With Missing Timesheet Counts

Hi @DJLight890 ,

According to your screen shot, it seems you didn't create any slicer with field [Period Name]... Then the condition "'Timesheet Hours'[Period Name] = SELECTEDVALUE ( 'Timesheet Hours'[Period Name] )" will not get the value, it cause the final value is blank. 

count of missing.jpg

Please create a slicer using the field 'Timesheet Hours'[Period Name] as below screen shot, then check if can get the correct value. Any comment or problem later, please feel free to let me know. Thank you.

slicer.JPG

Best Regards

Rena

Highlighted
Helper I
Helper I

Re: Help With Missing Timesheet Counts

@yingyinr  Thank you for your reply again. I'm sorry, in my screenshot, the "Period Name" slicer is hidden behind the dax dropdown, but I do have it setup. No matter which checkbox I use, I still see (Blank) Count of Missing. 

 

I copied and pasted your exact measure and even tried re-typing (in case of copy/paste error) exactly as you had it. Still seeing blank. I've tried reading more about some dax measures and I've tried variations of the following:

 

Pending Count = CALCULATE(COUNT('Timesheet Hours'[Employee Name]),
FILTER('Timesheet Hours',[Approval]="Pending"))

 

I was able to use the above to work for my Approved and Pending counts, but I realized that won't work for missing because the missing employees do not show up in 'Timesheet Hours[Employee Name]' because of the fact their timesheet is missing they won't ever appear in the table until it is submitted. 

 

So then I tried moving the [Approval] measure that I referenced in my very first post over to the 'Employee List' table (called [Approval2] and used this measure for missing counts:

 

Missing Count = CALCULATE(COUNT('Employee List'[Employee Name]),
FILTER('Employee List','Employee List'[Approval2]="Missing"))

 

This actually correctly counts the "Missing" employees when clicking on 1 single time period slicer, but if I try to use multiple slicers, the count is wrong (Ex: Jun22-28 has 2 missing, Jun 29-30 has 2 missing, but when I check both boxes, my missing count goes to 1 instead of the expected 4). 

 

Missing Count.PNG

 

Any thoughts? If you're able to provide any additional help I really appreciate it! Thank you for your previous time/help as well!

 

Highlighted
Community Support
Community Support

Re: Help With Missing Timesheet Counts

Hi @DJLight890 ,

Please try to update the formula of measure "Missing Count" as below:

Missing Count =
VAR _selections =
    ALLSELECTED ( 'Timesheet Hours'[Period Name] )
RETURN
    CALCULATE (
        COUNT ( 'Timesheet Hours'[Period Name] ),
        'Employee List'[Timesheet Required] = "Yes",
        FILTER (
            'Timesheet Hours',
            'Timesheet Hours'[Period Name] IN _selections
                && [Approval] = "Missing"
        )
    )

Best Regards

Rena

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors