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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

Please share some sample data in table Timesheet Hours and Employee List or your pbix file with us, then we can adjust the formula of related measures based on your scenario. Thank you.

Best Regards

Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous  Yes, here is the link to the file. I haven't done this on here before, so let me know if you have any issues getting it.

 

PowerBI File

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  Oh my goodness, thank you so much!!! That worked wonderfully! Looks like I need to learn more about VAR because that has been appearing a lot. Thanks again, I realy really appreciate your help!