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.

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

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”:

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

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.

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