Hello
So I created a weekly and monthly chart where I can see each employees contribution in work hours. On the charts there’s a static value of “potential” where every employee has a unique potential (35(realistic potential) and 40(official potential) on the chart).
Therefore my problem - is there a way to have a static modifier(potential) that is filtered by assingee and by week aswell. For example employee1 has a potential of 35h on W18 and W19, some days off W20 therefore the potential is lower etc.
How I calculate potential:
CASE [Logged by].CurrentMember.Name
– Eäri1–
WHEN “Employee1” THEN 0
WHEN “Employee2” THEN 35
WHEN “Employee3” THEN 40
END
Desired outcome:
Your current formula for the Potential calculation expects that the Logged by dimension must be used in the report. Therefore, the measures will be empty if the Logged by dimension is not used or the selected member does not match the name of the user mentioned in the condition.
The calculation must be elaborated further to adjust the Potential value for respective weeks. For instance:
CASE WHEN
DateBetween([Time.Weekly].CurrentHierarchyMember.StartDate,
DateParse('Jan 01 2021'),DateParse('Apr 01 2021')
)
THEN 35
ELSE 37
END
Hello
Thank you for your reply - it helped alot.
So when selecting all users I can display it like that - which works fine to display an overall trend.
CASE [Time.Weekly].[2021].Hierarchy
WHEN [Time.Weekly].[2021].[W18, May 03 2021] THEN 157
WHEN [Time.Weekly].[2021].[W19, May 10 2021] THEN 218
WHEN [Time.Weekly].[2021].[W20, May 17 2021] THEN 240
WHEN [Time.Weekly].[2021].[W21, May 24 2021] THEN 220
WHEN [Time.Weekly].[2021].[W22, May 31 2021] THEN 200
WHEN [Time.Weekly].[2021].[W23, Jun 07 2021] THEN 382.4
WHEN [Time.Weekly].[2021].[W24, Jun 14 2021] THEN 372
WHEN [Time.Weekly].[2021].[W25, Jun 21 2021] THEN 184.8
WHEN [Time.Weekly].[2021].[W26, Jun 28 2021] THEN 336
WHEN [Time.Weekly].[2021].[W27, Jul 05 2021] THEN 376
ELSE 0
END
The earlier mentioned solution does not work when I look at the hours spent by user. I am not sure how to tie in weekly time and users together in a CASE formula.
CASE WHEN
DateBetween([Time.Weekly].CurrentHierarchyMember.StartDate,
DateParse('May 31 2021'),DateParse('Jun 06 2021')
)
THEN 35
ELSE 40
END
This formula is generic and applies to everything and everyone. Is there a way to filter assinged potentials(these potentials are different by user aswell) by users?
Potentials vary by employee and week - so for example if I would choose User “1” then the CASE formula should filter by employee and display her/his potentials. If I choose User “2” then I would see his hours spent with his custom potentials (for example User 1 W18 25h, W19 35h, W20 0h, User 2 W18 20h, W19 30h, W21 10h).
You can expand further the formula with nested CASE statements like this:
CASE WHEN
DateBetween([Time.Weekly].CurrentHierarchyMember.StartDate,
DateParse('May 31 2021'),DateParse('Jun 06 2021')
)
THEN
CASE [Logged by].CurrentMember.Name
WHEN “Employee1” THEN 0
WHEN “Employee2” THEN 35
WHEN “Employee3” THEN 40
END
--next branches follow here
ELSE 40
END
It is, however, recommended that you check more automated solutions. If you have the employee rate list available in a spreadsheet or any other format available for additional data import you may follow this example:
.