Account for a Fluctuating Quantity within a Measure Month to Month

I have a working measure for teams utilization from month to month within a fiscal year. Each teams utilization gets divided by that teams unique number of resources. When that number of resources changes I am losing the appropriate history of the past month. For example, DW team had 3 resources then increased to 4 in Jan 2021 once I changed that number from 3 to 4 in my measure the data from July 2020 to Dec 2020 got skewed. I need a way to allow my measure to divide by 3 from July 2020 to Dec 2020 but divide by 4 from Jan 2021 onwards.

How can I appropriately reflect the data from month to month?

UtilizationCalc21 Measure:
CASE WHEN
DateCompare(
[Time].CurrentHierarchyMember.StartDate,
now()
) < 0
THEN
NonZero(Sum(Filter(
Descendants([Transition Status].CurrentHierarchyMember,[Transition Status].[Transition Status]),
NOT IsEmpty([Measures].[Issues history])
),
CASE
WHEN [Transition Status].CurrentHierarchyMember.Name = “In Progress” OR
[Transition Status].CurrentHierarchyMember.Name = “At Risk”
THEN
[Measures].[Resource #1 % Allocated history]+
[Measures].[Resource #2 % Allocated history]+
[Measures].[Resource #3 % Allocated history]+
[Measures].[Resource #4 % Allocated history]+
[Measures].[Resource #5 % Allocated history]+
[Measures].[Resource #6 % Allocated history]+
[Measures].[Resource #7 % Allocated history]+
[Measures].[Resource #8 % Allocated history]+
[Measures].[Resource #9 % Allocated history]+
[Measures].[Resource #10 % Allocated history]

WHEN [Transition Status].CurrentHierarchyMember.Name = “Completed” AND
(
[Measures].[Issues history],
[Resolution].[Completed]
) > 0
THEN
[Measures].[Resource #1 % Allocated change]+
[Measures].[Resource #2 % Allocated change]+
[Measures].[Resource #3 % Allocated change]+
[Measures].[Resource #4 % Allocated change]+
[Measures].[Resource #5 % Allocated change]+
[Measures].[Resource #6 % Allocated change]+
[Measures].[Resource #7 % Allocated change]+
[Measures].[Resource #8 % Allocated change]+
[Measures].[Resource #9 % Allocated change]+
[Measures].[Resource #10 % Allocated change]

END
)) / 100 /
CASE
WHEN [Application].CurrentMember.Name MATCHES “A&C” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
END

Utilization Measure:
CASE WHEN
DateCompare(
[Time].CurrentHierarchyMember.StartDate,
now()
) < 0
THEN
CASE WHEN
[Time].CurrentHierarchyMember.Level.name = “Day”
THEN
Val([Measures].[Utilization Calc21]/([Measures].[Day Count]))
ELSE
Val(sum(
Filter(Descendants([Time].CurrentHierarchyMember, [Time].[Day]),
[Measures].[Utilization Calc21]>0
),
[Measures].[Utilization Calc21])/([Measures].[Day Count]))
END
END

@Alyssa_A

If “Application” is a single-select custom field in Jira, you could import the team size to each team from the source file. That requires importing the dimension with separate_table = true setting in advanced settings (that would mean double import for the field, to reset the default settings).

Then you could import additional data for Application dimension members with the dates when team size changed to a new value and the value of the new team size.
See attached image below.
When the new field is imported in eazyBI, you could create a new user-defined calculated measure “Team size history” that would return the last value imported for each month.

Cache(DefaultContext((
    [Measures].[Team Size],
    [Application].CurrentMember,
    -- find last month with team size history
    Tail(Filter(
      [Time].[Month].Members.Item(0):
      Tail(Descendants([Time].CurrentMember,
        [Time].[Month])).Item(0),
      -- filter months with team size history
      Cache(NOT IsEmpty(DefaultContext((
        [Measures].[Team Size],
        [Application].CurrentMember,
        [Time].CurrentMember
      ))))
    )).Item(0)
  )))

Later you could update your final calculated measure and remove the whole “Case When…Then…End” section where you define manually the team size for each application, instead use the “Team size history” measure there in the formula

In the measure "UtilizationCalc21 " replace this code

CASE
WHEN [Application].CurrentMember.Name MATCHES “A&C” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END

with the following

[Measures].[Team size history] --this is created in previous steep as new user-defiend measure

Martins / eazyBI

Thank you Martin! I’m having our admin look into importing the team size as you have described. In the meantime I was working on another approach for this that I’m wondering if it is viable but I am receiving a loading error.

I created a new calculated measure, Resource Value…
CASE
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Jul 2020” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 2
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 5
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Aug 2020” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 2
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 5
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Sep 2020” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 2
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 5
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Oct 2020” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 2
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 5
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Nov 2020” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 2
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 5
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Dec 2020” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 5
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Jan 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 5
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Feb 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 5
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Mar 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Apr 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “May 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Jun 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Jul 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Aug 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 1.5
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Sep 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 2.6
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 11
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Oct 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 2.6
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 11
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Nov 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 2.6
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 11
END
WHEN [Time.Fiscal].CurrentMember.Name MATCHES “Dec 2021” THEN
CASE
WHEN [Application].CurrentMember.Name MATCHES “Automation” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “DW” THEN 4
WHEN [Application].CurrentMember.Name MATCHES “HIE” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “HIX” THEN 7
WHEN [Application].CurrentMember.Name MATCHES “MA21” THEN 3
WHEN [Application].CurrentMember.Name MATCHES “MH Sub-Systems” THEN 2.6
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 11
END
END

That I then added it to my existing Utilization Calc measure…
CASE WHEN
DateCompare(
[Time].CurrentHierarchyMember.StartDate,
now()
) < 0
THEN
NonZero(Sum(Filter(
Descendants([Transition Status].CurrentHierarchyMember,[Transition Status].[Transition Status]),
NOT IsEmpty([Measures].[Issues history])
),
CASE
WHEN [Transition Status].CurrentHierarchyMember.Name = “In Progress” OR
[Transition Status].CurrentHierarchyMember.Name = “At Risk”
THEN
[Measures].[Resource #1 % Allocated history]+
[Measures].[Resource #2 % Allocated history]+
[Measures].[Resource #3 % Allocated history]+
[Measures].[Resource #4 % Allocated history]+
[Measures].[Resource #5 % Allocated history]+
[Measures].[Resource #6 % Allocated history]+
[Measures].[Resource #7 % Allocated history]+
[Measures].[Resource #8 % Allocated history]+
[Measures].[Resource #9 % Allocated history]+
[Measures].[Resource #10 % Allocated history]

WHEN [Transition Status].CurrentHierarchyMember.Name = “Completed” AND
(
[Measures].[Issues history],
[Resolution].[Completed]
) > 0
THEN
[Measures].[Resource #1 % Allocated change]+
[Measures].[Resource #2 % Allocated change]+
[Measures].[Resource #3 % Allocated change]+
[Measures].[Resource #4 % Allocated change]+
[Measures].[Resource #5 % Allocated change]+
[Measures].[Resource #6 % Allocated change]+
[Measures].[Resource #7 % Allocated change]+
[Measures].[Resource #8 % Allocated change]+
[Measures].[Resource #9 % Allocated change]+
[Measures].[Resource #10 % Allocated change]

END
)) / 100 /
[Measures].[Resource Value]
END

Which is then used in my Utilization measure…
CASE WHEN
DateCompare(
[Time].CurrentHierarchyMember.StartDate,
now()
) < 0
THEN
CASE WHEN
[Time].CurrentHierarchyMember.Level.name = “Day”
THEN
Val([Measures].[Utilization Calc]/([Measures].[Day Count]))
ELSE
Val(sum(
Filter(Descendants([Time].CurrentHierarchyMember, [Time].[Day]),
[Measures].[Utilization Calc]>0
),
[Measures].[Utilization Calc])/([Measures].[Day Count]))
END
END

My admin and I set up the Team Size measure and Team Size History measure but it does not look to be adjusting to what I truly need. My question is if the Team Size History measure will return the last value imported for each month will this also use the team size that is associated with a past or current month? So with the Team Size History we setup below will my Utilization Calc21 measure divide by the team size per month value?

Using your recommended approach, I am getting INFINITY as my values for every month. Can you assist?

@Alyssa_A

Please reach out to eazyBI support@eazyBI.com.
They will help you debug the calculation step by step to see why Infinity values are returned in your report.

Martins / eazyBI