Creating hours spent by specific users and creating dynamic date logged by group

Questions & Answers
1

Hi,

I wanted get a month KPI for issues with resolution date and spent hours for some specific users.

For this I’ve created a group for logged_by and used that group to create a new hours spent formula with take into account only the spent time by the users from the group.

However, the components of the group change from time to time and I have to exclude/include some users for different periods. I tried in the end without success the following:

Aggregate(
 CASE

   WHEN [Time].CurrentMember.MemberValue < DateSerial(2025, 6, 1) THEN

    {[Logged by].[User_A],
     [Logged by].[User_B],
     [Logged by].[User_C]}

   WHEN [Time].CurrentMember.MemberValue <= DateSerial(2025, 6, 30) AND
        [Time].CurrentMember.MemberValue >= DateSerial(2025, 6, 1) THEN

    {[Logged by].[User_A],
     [Logged by].[User_D]}

   WHEN [Time].CurrentMember.MemberValue >= DateSerial(2025, 7, 1) THEN
    {[Logged by].[User_A],
     [Logged by].[User_B],
     [Logged by].[User_C],
     [Logged by].[User_D]}
   
   ELSE
    {}     
 END
)

This yields the warning
image
and then the error
image

Please let me know how to fix this, or what other option do I have.

2

Hi @Radmar

It is not recommended to create members in Dimensions (other than Measures) that reference the members of other dimensions (like Time) as it may return unexpected results.

In this case, the correct approach would be to create a measure in the Measures dimension to check for the date condition and return a measure for the expected Logged by set.

Could you please share which dimension are you using in the report Rows and which measures are you currently using from the Measures dimension? Are there any Page filters as well?

I’ll check if there are some options to create a formula for your report context.

Thanks!
​Best regards,
​Nauris

3

Hi @nauris.malitis,

Now my report looks like this:

image
image1347×514 29.8 KB

where

image
image650×272 8.53 KB

with Ro_coders defined as a group from Logged_by as in the button capture.

image
image705×235 9.16 KB

image
image471×211 7.88 KB

and

image
image442×306 10.5 KB

I did not used filters. Maybe could be a solution using them…

So, the Ro_coders lists varies from time to time and I have to capture somehow this in KPI.

Thanks you,
Radu

4

Hi, @nauris.malitis,

Did help you my last post? Please let me know if I have to add more details.

Regards,
Radu

5

Hi @Radmar

My apologies, I was on vacation at the time of your reply and completely missed it!

On the most part, everything looks okay with your formulas.
The only thing to note is that the “Coders hours spent” is divided by the total number of “Issues with Realisation date” at that same period, no matter if those issues have any hours spent or not. This could be checked with a bit more complex calcualtion.

Do you need help with that or is there anything else that you’re trying to solve?

Again, my apologies for the long wait!
​Best regards,
Nauris

6

Hi, are you thinking here to verify that “Issues with Realisation date” should be greater than 0, are you have in mind something else for “complex calculation”?

I could add that check easily, but I do not guess what complex calculation suppose to fix.

Radu

7

Hi @Radmar

Could you please try the following formula?

Avg(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue Realisation date],
      [Time].CurrentHierarchyMember
    )
  ),
  CASE WHEN
    (
      [Measures].[Coders hours spent],
      [Time].CurrentHierarchy.DefaultMember
    ) > 0
  THEN
    (
      [Measures].[Coders hours spent],
      [Time].CurrentHierarchy.DefaultMember
    )
  END
)

​Best regards,

Nauris

8

Hi @nauris.malitis

Using “suggested” formula yield the followings:

image
image655×386 28.6 KB

It looks like “suggestion” yields different results than average issue cost. So, what is actually computing the suggestion formula?

The 3 month average is computed as:

Sum(
 LastPeriods(3, [Time].CurrentHierarchyMember),
 [Measures].[Coders hours spent]
) /

(Sum(
 LastPeriods(3, [Time].CurrentHierarchyMember),
 [Measures].[Issues with Realisation date]
) + 0.0000000001)

Regards,

Radu

9

Hi @Radmar

The formula I suggested goes through all issues, selects only those that have an “Issue Realisation date”, and then only calculates the average of those issues that have any hours spent by coders.

It’s different from your original formula that divided the hours spent by coders by all issues with a Realisation date (even those that don’t have any hours spent).

The question is- should the coder hours be divided by all “Issues with Realisation date” or only by those “Issues with Realisation date” that also have coder hours?

Nauris

10

Hi @nauris.malitis

Thanks for explanation. I will think about what it should be computed.

Regards,

Radu