Forum Discussion
Filtering two criteria referencing different days within a week
I'm new to PowerPivot and am struggling with a filter that includes two criteria that reference different days within a week (the pivot report is summarized by week).
My data includes labor hours by person, date, activity, and "exceptions" (comment field).
An employee receives double time on Sundays if they either 1) worked a minimum of 6 hours on Saturday, or 2) were called in to work (an "exception"). Otherwise they receive time and a half.
I have measures for Saturday and Sunday hours... for examplte CALCULATE(sum(PayrollHours[Hours]),'Calendar'[DayOfWeek]=7)
The following formula (with and without "ALL") does not find any hours...
=CALCULATE( [SundayHours] ,
FILTER( ALL(PayrollHours) , [SaturdayHours] >= 6 ) ,
FILTER( PayrollHours, PayrollHours[Exceptions] = BLANK() )
)
I have also tried the following statement, which works except in cases were an "Exception" is on a day other than Sunday (it appears to evaluate for the entire week.)
=IF( AND( [SaturdayHours] >= 6 ,MIN( PayrollHours[Exceptions] ) = BLANK() ) ,
[SundayHours] ,
BLANK()
)
I have tried DATEADD to look at the day before Sunday, but this actually shows me the Sunday prior to the current Sunday (again, the pivot table is summarized by week, not day, which seems to complicate things).
Thank you,
Jim
I found a work around. I used two measures instead of one...
SundayNoExceptions = CALCULATE( [SundayHours] , PayrollHours[Exceptions] = BLANK() )
DoubleHrsSunIfSat>6Hrs = IF( [SaturdayHours] >= 6 , [SundayHoursNoExceptions] , BLANK() )
2 Replies
- Jim_2928Regular Visitor
I found a work around. I used two measures instead of one...
SundayNoExceptions = CALCULATE( [SundayHours] , PayrollHours[Exceptions] = BLANK() )
DoubleHrsSunIfSat>6Hrs = IF( [SaturdayHours] >= 6 , [SundayHoursNoExceptions] , BLANK() )
- v-jiascu-msft
Microsoft Employee
Hi Jim,
That's great. If you need further support, please provide a sample of your raw data.
Best Regards,
Dale