Good Day all,
I need a nudge in the right direction to solve a problem with what should be a very simple calculation.
Some set up information:
My report uses two slicers, Company and Location and a filter on all pages to limit the year to 2020.
I have been asked to add work hours and overtime analysis to my report.
Time card data is held in an SQL server table and I have added it to my data model as shown below. I consider it essentially the same as the HR Actions table as it will contain data for some of the employees in the employee table, but not all.
I Then created the following measures:
Total Hours = SUM(Timecard[HOURS])
Work Hours =// description column only has data for non-worked hours, i.e. vacation / leave
Timecard[DESCRIPTION] = BLANK()
As I was creating the measures, I used a table to display the results and tested my slicers against the data and all was working great. The table was correctly filtered with all combinations of the slicers.
Obviously next I needed to calculate overtime on a weekly basis. As weekending date was present in my dates calendar, and overtime is calculated on a weekly basis I placed the week ending date in the table and dropped the below calculation into the table visual to calculate weekly overtime.
Overtime Hours =
IF([Work Hours] > 40,
[Work Hours] - 40,
When I place this formula in the table, all of the slicers cease to function correctly and I do not understand why. The Position ID’s that are “correct” have results in the measures, incorrect position ID’s are all blank for the measures.
I feel I am missing something fundamental…but I just don’t see it. Can someone point me towards a topic or tutorial that can help me understand why this is happening?