Using daily values for a monthly measure and dividing by the number of days in the month

Questions & Answers
1

I’m wondering if I’m heading in the right direction and how to correctly divide by the current number of days in the month…

I identified that I need each month to represent the sum of the daily values divided by the current number of days in the month. For example, I need Apr 2021 to sum those daily values being calculated by a measure called “Utilization A” and divide by 28 (todays current day count for the current month). Tomorrow it would need to divide by 29.

image
image850×716 59.7 KB

So I first started by creating a new measure that uses “Utilization A”. Is this the right way to use those daily values I need? And how can I correctly divide by the days in the month like the example above instead of the 31 I have in the measure? And how can I correctly divide by the days in the month in a situation where September only had a handful of days with actual work being done; see screenshot below showing I would only need to divide by 6 days?

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

image
image1138×370 31.9 KB

2

Hi @Alyssa_A
To get how many days in a month (at day level) have some value at the measure you are interested in, you can use the function MTD. It returns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member (this will count days till day level in a particular month):

NonZero(Count(
  Filter(MTD([Time].CurrentMember),
  [Measures].[Issues created]> 0)
))

To get day count with measure at other levels you can use this formula:

NonZero(Count(
  Filter(Descendants([Time].CurrentMember, [Time].[Day]),
  [Measures].[Issues created]>0)
  ))

If you would use it in one measure, it would be something like this:

CASE WHEN
  [Time].CurrentMember.Level.Name = "Day"
THEN
  NonZero(Count(
  Filter(MTD([Time].CurrentMember),
  [Measures].[Issues created]> 0)
  ))
ELSE
  NonZero(Count(
  Filter(Descendants([Time].CurrentMember, [Time].[Day]),
  [Measures].[Issues created]>0)
  ))
END

Here you can see how it counts days for my measure “Issues created”:

eazyBI_Day_count
eazyBI_Day_count1568×2184 349 KB

But as you haven’t provided the formula for your measure “Utilization A” and what is the business requirement for your final measure, then I cannot add more details currently about a possible solution.

Let me know if you have more questions about this!

best,
Gerda // support@eazyBI.com

3

I think I’ve made some progress but still need some help dividing by the correct number of active days or days that have data.
Utilization A now named Utilization Calc measures each teams utilization. I need to take those daily values and average them by the number of days with data per month.

Utilization Calc:
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 2
WHEN [Application].CurrentMember.Name MATCHES “MMIS” THEN 10
END
END

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

The Utilization measure its dividing by the total numbers of the month but I need it to divide by the days that have data. For example, May only has 17 days of data right now but its being divided by 31 days of the month.

image
image782×664 7.37 KB

4

I was able to apply the day count you explained. Thank you so much.

NonZero(Count(
  Filter(Descendants([Time].CurrentMember, [Time].[Day]),
  [Measures].[Utilization Calc]>0)
  ))
1 Like