Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two unrelated tables, one representing a time card and the other representing all calendar work days. I am attempting to calculate the number of hours a person could have worked versus the number of hours they recorded.
This is the Measure I'm using:
TotalWorkingHours = CALCULATE(SUM('Calendar'[Workable Hours]), FILTER('Calendar','Calendar'[Date] IN ALLSELECTED('Employee Labor Detail'[WorkDate])))
However, the result is not correct when the WorkDate slicer spans the entire range of dates in the "Employee Labor Detail" table for a given employee.
For example, Employee A has recorded hours between 1/2/2018 to 1/22/2018. If I have the Date Slicer span that entire range, the TotalWorkingHours measure reports 624 hours. If I change the slicer to select dates between 1/2/2018 and 1/21/2018, I get the expected answer: 98 hours. Adding one more day should not add 500+ hours.
This issue occurs for all employees whose last recorded work date is less than the last recorded work dates of any employee.
The same issue occurs if the employee's first recorded work date is after 1/2/2018. The date slicer displays the correct work date range, but the measure sums all work dates before the first selected unless I manually add one day to the slicer.
Is there something going on with ALLSELECTED that is causing it to return values before or after what is shown in the slicer?
I pasted screenshots to imgur: https://imgur.com/a/07hFrW0
Hi @AdamOrmond,
Please try this measure:
TotalWoringHours =
CALCULATE (
SUM ( Calendar[Workable Hours] ),
FILTER (
ALL ( Calendar ),
Calendar[Date] >= MIN ( 'Employee Labor Detail'[WorkDate] )
&& Calendar[Date] <= MAX ( 'Employee Labor Detail'[WorkDate] )
)
)
Regards,
Yuliana Gu
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
User | Count |
---|---|
140 | |
113 | |
104 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |