Forum Discussion
Multiply single value across date range in report
- Anonymous8 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.
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!
- Anonymous8 years agoNot applicable
Fantastic result. Good to see you came up with a novel solution.