Future Months Shouldn't be Calculating

Questions & Answers
1

Hello, I’m calculating teams utilization over the past and current months within our fiscal year. I need all months in the fiscal year (July to June) to display but data should not yet be displaying in the future months until todays date is within that month. I’m not sure what I need to change in the report or my measure to only present the data for past months and the current month. For example, today is April 5th so we should have data calculating through April but not for May and June.

image
image1920×867 93 KB

NonZero(Sum(
Filter(
Descendants([Transition Status].CurrentHierarchyMember,[Transition Status].[Transition Status]),
Not IsEmpty([Measures].[Issues history])
),
CASE
WHEN [Transition Status].CurrentHierarchyMember.Name = “Completed” AND [Measures].[Transitions to status issues count] > 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]

WHEN [Transition Status].CurrentHierarchyMember.Name = “In Progress” OR [Transition Status].CurrentHierarchyMember.Name = “At Risk” AND [Measures].[Issues history] > 0
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]

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 “Cross Project Support & Administration” THEN 2
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 2
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
WHEN [Application].CurrentMember.Name MATCHES “OPDM” THEN 2
END

2

Hi @Alyssa_A,

You can wrap calculations in following condition to show only until “today”:

CASE WHEN
  DateCompare(
    [Time].CurrentHierarchyMember.StartDate,
    now()
  ) < 0
THEN
-- your formula here
END

Lauma / support@eazybi.com

3

Thank you so much, this is exactly what I needed!

1 Like