Need help for weekly target that is applied to any date range + conditional formatting

[gill_133]

Hi there,

I'm having trouble getting my head around the steps required for a visualisation relating to staff time. I'm fairly new to this so please bear with me. I haven't come across a forum example that directly relates to the below. 

Staff have a daily and weekly minimum contracted hours value (not the same for all staff - some are part-time). My table visualisation shows total staff time and I've got a date slicer that can be used to select any number of previous days/weeks/years. Happy with that. 

I'm not great on DAX so the next step I'm not sure of - I'd like to use conditional formatting to show in red where the slicer-selected total hours for any staff exceed EITHER the daily contracted hours, OR the weekly contracted hours. What is the DAX required by a measure that could potentially do this? (is it even possible? if not, just having the weekly exceeded value shown will do)

Additional note - staff log hours for various tasks per day; I am looking to sum all time (i.e. from all tasks) over the selected period. 

Please see below example of my data for a single staff member and expected result:

Table: Staff time requirements

Name	Total contract hours per week	Total contract hours per day	Days worked per week
John Bloggs	37.5	7.5	5

Table: Logged time

Name	Time (hours)	Task	Date
John Bloggs	9.5	Consulting	21/07/2023
John Bloggs	7	Administration	20/07/2023
John Bloggs	10	Consulting	19/07/2023
John Bloggs	5	Training	18/07/2023
John Bloggs	3.5	Promotion	18/07/2023
John Bloggs	7.5	Consulting	17/07/2023
John Bloggs	8	Administration	14/07/2023
John Bloggs	12	Consulting	13/07/2023
John Bloggs	1	Consulting	12/07/2023
John Bloggs	7	Training	12/07/2023
John Bloggs	6	Promotion	11/07/2023
John Bloggs	6	Consulting	11/07/2023
John Bloggs	9	Consulting	10/07/2023

Expected result: (based on slicer selection of last 2 calendar weeks)

Staff	Contract hours for period	Sum of time (hours)
John Bloggs	75	91.5

Many thanks for any help!

Note - I am a new user and don't have the option to upload example files - hence inclusion as pasted above.

My two example excel files and WIP pbix can be downloaded here: https://www.dropbox.com/scl/fo/vhixn9z55qz8wp8nxj6pr/h?rlkey=gcxvuy1d8hubak1ki6jh8168p&dl=0 

[v-cgao-msft · Accepted solution]

Hi @gill_133 ,

Please create 3 measures:

Total hours = SUM('Staff time logged'[Time (hours)])

Contract hours for period = 
VAR _min_date = MIN('Staff time logged'[Date])
VAR _max_date = MAX('Staff time logged'[Date])
VAR _weeks = DATEDIFF(_min_date,_max_date,WEEK) + 1
VAR _total = _weeks * MAX('Staff time targets'[Total contract hours per week])
RETURN
_total

Measure = IF([Total hours]>[Contract hours for period],"Red")

Apply the measure to the conditon formatting:

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[gill_133]

@v-cgao-msft thanks so much, this worked perfectly - just what I needed. 

One further query - how would I amend the 'contract hours per period' measure if I had a second staff member with total contract hours per week of 30? (i.e. in that case, the 'max' function would only see the 37.5 value).

Kind regards, appreciate any insights. 

[foodd]

How to Get Your Question Answered Quickly  

[gill_133]

Thanks, I've tried to follow the directions in that post as closely as possible in my original post - please let me know how I could improve or if I've done something amiss. 

[foodd]

Please add your work-in-progress Power BI Desktop file, and source data file in Excel format using tables.   Again, this is described in the following post:  How to Get Your Question Answered Quickly  

[gill_133]

Got it - I'm a new user and can't attach files, but have updated post above with link to dropbox to download WIP pbix and excel ss files.