Forum Discussion
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
Column 2: Hours Expected Per Day
Table: Timesheets
Column 1: Time_ID
Column 2: Hours Logged
Column 3: Date Logged
Column 4: FK_Emp_ID
- Have attached screen shot example.
As per the data in the image, want to create a report / chart that would give me the following result over a time period (example 2 days):
Jim, Expected Hours: 16, logged hours 4.
Jane, Expected Hours: 16, logged hours 4.
The logged hours are accociated with a date, so will SUM easily, however since the Hours Expected by Day is a single value, it's value will remain the same and I instead end up with the following:
Jim, Expected Hours: 8, logged hours 4.
Jane Expected Hours: 8, logged hours 4.
I will always get Expected Hours: 8, no matter what the date range.
Is there anyway I can multiply that single value by each day in the date range? I'm stumped on an approach to it.
Thanks
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.
3 Replies
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.Anonymous Yes, thank you! That does make a lot of sense!
I do need this to include days not worked on, so I created the following Work Days table also: https://ibb.co/cornqw
This runs from 2005 through to about 2025.
I have altered your Measure by using the following:
Expected Hours = SUM(Employees[Hours Expected Per Day]) * SUM('Work Days'[Working Day])This seems to be working great. Thanks for your help!
