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

[Radmar]

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

and then the error

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

[nauris.malitis]

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

[Radmar]

Hi @nauris.malitis,

Now my report looks like this:

where

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

and

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