Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AdamOrmond
Frequent Visitor

Date Slicer, Disconnected Calendar, and Summation

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

 

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.