Forum Discussion

jimmyd33's avatar
jimmyd33
Regular Visitor
8 years ago
Solved

Multiply single value across date range in report

I am trying to create a report where I can run expected hours for employees to log vs what they have logged.   Example of tables / data:   Table: Employees Column 1: Emp_ID Column 2: Name Colu...
  • Anonymous's avatar
    Anonymous
    8 years ago

    You can, we just need to create a measure to do the work for you.

     

    The dax code you want is a distinct count of the date field, to work out how many days you have logs for.  Take that figure and multiply it to the expected hours, which you can do inside a measure.  So when you build your table you will select the Name column, the Expected Hours measure, and then your logged hours column which is selected to SUM.

     

    Your expected hours measure would look something like:

    Expected Hours = SUM(Employees[Hours Expected Per Day]) * DISTINCTCOUNT(Timesheets[Date Logged])

    The metric doesn't look like it should work, but since we know how we are going to use it, we know that once placed into the table the SUM will only have the context on a single Employee and whichever date range you have selected.


    EDIT:  1 downside to this quick approach, if he did not work on a day and was expected to, this formula won't include that day in the expected hours.  If you want to include that, we can alter the distinct count to count working days, or calander days or whatever metric you want to check.