Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:

 

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!

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    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
            )
        )

     

     

    Best Regards

    Rena

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    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"
    )
)

    Best Regards

    Rena

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous  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?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ，

        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. 

        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.

        Best Regards

        Rena

  • marceloVVR's avatar
    marceloVVR
    Helper I
    6 years ago

    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)

    😶

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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):

       

       

      Thanks!

      • marceloVVR's avatar
        marceloVVR
        Helper I
        6 years ago

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

        try:

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