Compliance based in logged or not logged hours

[mscabrera]

Hi everyone

I've been having troubles trying to solve the following issue

I need to calculate and present the compliance % based on if the user logged hours in a week (but should work for any period of time, months, quarter, year) 

SAMPLE DATASET:
https://we.tl/t-eNKUcWl8W0

the expected outcome is something like this for the Name2 from the sample dataset

Week	Logged	Pending
1/1/2024 - 1/7/2024		1
1/8/2024 - 1/14/2024		1
1/15/2024 - 1/21/2024		1
1/22/2024 - 1/28/2024		1
1/29/2024 - 2/4/2024		1
2/5/2024 - 2/11/2024		1
2/12/2024 - 2/18/2024		1
2/19/2024 - 2/25/2024	1
2/26/2024 - 3/3/2024	1
3/4/2024 - 3/10/2024	1
3/11/2024 - 3/17/2024	1
3/18/2024 - 3/24/2024	1
3/25/2024 - 3/31/2024		1

If the user log at least one hour in the selected period, it should count as 100% in the prior example for the Name2, the compliance is 38,46%, because the user logged at least one hour in 5 weeks of the 13 weeks of current year (I have a column that just filter the data until current day/week) . 5/13 = 38,46%.

This needs to be scalable and presented in a stacked column chart with a legend that shows the logged and the pending hours.

Hope someone can help me with this.

[mscabrera]

@Anonymous  I just found that for the compliance% the measure is not taking into account the date the person started (dimEmployees[Start Date]) the denominator of compiance% should count the weeks from when the employee started, and ignore the previous ones. Is there a way to incoporate something like this in the formula:

FILTER(

        dimDate,

        dimDate[Date] <= MAX(dimDate[End Date2]) && dimDate[Date] >= MIN(dimDate[Start Date])

    ),

[Anonymous · Accepted solution]

Hi @mscabrera ,

Please try code as below to create measures.

Compliance % = 
VAR _CountLogged = COUNTX(SUMMARIZE(LoggedHours,dimDate[CustomWeek],dimEmployees[Name]),[Logged])
VAR _CountWeek = COUNTAX(VALUES(dimDate[CustomWeek]),[CustomWeek]) * DISTINCTCOUNT(dimEmployees[Name])
RETURN
DIVIDE(_CountLogged,_CountWeek)

Pending = 
 IF(
    [Logged] = 0,
    1, 0
 )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mscabrera]

Hi @Anonymous 

Thank you so much for your response, it works perfect!! although I have to mention that the only downside is the visualization in the bar chart doesn't reflect  numbers of the compliance % when seeing it for all names, fo a specific user (name2) it shows the weeks when the user logged hours and when he didn't, but when clearing the filter it shows everything as logged except the week when no one logged hours

It should show me that for the first 7 weeks of the year, 2 users (name1 & name2) didn't logged hours out of the 8 users, but I guess this has something to do with the way the measure is set, in order to have it the way I wanted, the Logged and Pending status would need to be in the table and added to the bar chart as a legend, right?