Latest hourly rate based on time

Questions & Answers
, ,
1

Hi everyone, I need some help here. I’ve imported some CSV files to get the hourly rate by user and calculate the cost of the projects based on spent hours. However, the hourly rate might change over time, and when I use a time filter, I can’t retrieve the recent hourly rate based on the filter.

For example, the user Paulo changed the hourly rate in May. When I use the ‘all time’ filter, the report sums up all hourly rates instead of showing the latest one. How can I get the correct rate? If I filter for May, it should show 40, and if I filter for April, it should show 55 because it’s the latest rate.
And i would like to if i set all times in filter i can calculate the cost considering the rate changes in time for example:
may a paulo rate is 50 and he logged 10 hours
in jun the changed rate to 100 and he logged more 10 hours so the total cost is 500+1000=1500 in all time

image
image1348×464 17.3 KB

image
image1348×464 22 KB

2

Hi @Viol1914,

The expression has to look at the past periods to see the latest one with the rate change for the selected user.

The expression to find the “applicable rate” might be as follows.

Sum(
--the latest one month 
 Head(
--order months with rate change to find the latest
 Order(
--filter months to identify ones with rate change
  Filter(
--set of months within latest previous period having rate change
   DescendantsSet(
--the latest major period with rate change
    Head(
     Order(
      Filter(
--set of periods before
       PreviousPeriods(
--find the next month so that previous periods include the current one
        [Time].CurrentHierarchyMember.NextMember),
--filter previous (and current) periods to find the one with rate change
       [Measures].[Hourly rate]>0
       ),
--arrange descending by start date
      [Time].CurrentHierarchyMember.StartDate,
      DESC),
--retrieve the latest period with rate change
     1),
--dive into latest period with rate change to level of month
    [Time].[Month]),
--identify the month with rate change
   [Measures].[Hourly rate]>0),
--arrange descending by start date
   [Time].CurrentHierarchyMember.StartDate,
   DESC),
--retrieve the latest month with rate change
  1),
--use the rate change of that month
 [Measures].[Hourly rate]
)

The cost for a specific month would then be the multiplication of hours spent and the applicable rate.
Since the search for applicable rates might take some calculation resources, the applicable months should be filtered for longer periods.

The expression for finding the cost for the selected user over the selected Time period might be as follows.

Sum(
 Filter(
--set of months within selected period
  DescendantsSet(
    [Time].CurrentMember,
    [Time].[Month]),
--only months with spent hours are taken    
    [Measures].[Hours spent]>0),
--numeric value for sum - cost for user in month
  [Measures].[Hours spent]
  *
  [Measures].[applicable rate]
)

Since you might as well look for the total costs of the selected team or the selection of users, you might need to iterate through the set of users.

And again, the costs should only be calculated for users with applicable hours.
The expression might then be as follows.

Sum(
--only use users that have spent hours in period
  Filter(
--set of (selected) users
    DescendantsSet(
      [Logged by].CurrentMember,
      [Logged by].[User]),
--users have logged time
    [Measures].[Hours spent]>0),
--numeric value for sum - cost for user in periods
  Sum(
   Filter(
--months of the period
    DescendantsSet(
     [Time].CurrentMember,
     [Time].[Month]),
--where time was logged
    [Measures].[Hours spent]>0),
--the cost for user in month    
    [Measures].[Hours spent]
     *
    [Measures].[applicable rate]
  )
)

Regards,
Oskars / support@eazyBI.com

3

Hi Oskars, the solution works fine when I only filter by time on the page. However, when I filter by projects or project lead, the column doesn’t show the cost calculation for the projects. I need to see the project costs based on the hours worked by the users. How can I adjust the formula to work with the project filter or project lead filter?

4

Hi @Viol1914 ,

The Hourly rate is only mapped to the Logged By dimension.
It is not mapped to Project or Project Lead dimensions. Therefore, the “Hourly rate” comes out empty when an explicit member of those dimensions applies.

You might reset these dimensions when calling the “Hourly rate” or “Applicable rate” measures.

Cost overs users over time might be as follows.

Sum(
--only use users that have spent hours in period
  Filter(
--set of (selected) users
    DescendantsSet(
      [Logged by].CurrentMember,
      [Logged by].[User]),
--users have logged time
    [Measures].[Hours spent]>0),
--numeric value for sum - cost for user in periods
  Sum(
   Filter(
--months of the period
    DescendantsSet(
     [Time].CurrentMember,
     [Time].[Month]),
--where time was logged
    [Measures].[Hours spent]>0),
--the cost for user in month    
    [Measures].[Hours spent]
     *
    ([Measures].[applicable rate],
--reset the dimensions not mapped for the Hourly rate using .CurrentHierarchy.DefaultMember
     [Project].CurrentHierarchy.DefaultMember,
     [Project Lead].CurrentHierarchy.DefaultMember)
  )
)

regards,
Oskars / support@eazyBI.com

1 Like
5

Thank you very musch Oskars, it work’s