Eazybi reporting

[Dhivakar C]

Hi We need to include saturday as a working day for calculating the planned estimates of the projects. I have used the below MDX code but can u please help to modify the code to include saturday as working day.

CASE WHEN
-- show for default time periods on workingdays only
([Time].CurrentHierarchyMember.Level.Name = "Day"
and
NOT CoalesceEmpty([Time].CurrentHierarchyMember.Get("Week day name"),"") MATCHES "Saturday|Sunday")
or [Time].CurrentHierarchyMember.Level.Name MATCHES "Year|Week|Month|Quarter"
or [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
NonZero(SUM(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
-- check if an issue has start and end date, and it falls into selected period:
NOT IsEmpty([Measures].[Issue Target start])
AND
NOT IsEmpty([Measures].[Issue Target end])
AND
(DateBetween(
[Time].CurrentHierarchyMember.StartDate,
[Measures].[Issue Target start],
[Measures].[Issue Target end])
OR
DateInPeriod(
[Measures].[Issue Target start],
[Time].CurrentHierarchyMember)
)),
-- workload per day = total planned time / workdays in planned period
-- total planned time
([Measures].[Original estimated hours],
[Time].CurrentHierarchy.DefaultMember)
/
-- workdays in planned period
DateDiffWorkdays(
DateWithoutTime([Measures].[Issue Target start]),
DateAddDays(DateWithoutTime([Measures].[Issue Target end]),1)
)
*
-- full workdays in period - workdays in weeks, month, etc.
DateDiffWorkdays(
TimestampToDate(IIF(DateInPeriod([Measures].[Issue Target start], [Time].CurrentHierarchyMember),
DateToTimestamp(DateWithoutTime([Measures].[Issue Target start])),
DateToTimestamp([Time].CurrentHierarchyMember.StartDate))),
TimestampToDate(IIF(DateInPeriod([Measures].[Issue Target end], [Time].CurrentHierarchyMember),
DateToTimestamp(DateAddDays(DateWithoutTime([Measures].[Issue Target end]),1)),
DateToTimestamp([Time].CurrentHierarchyMember.NextStartDate)))
)
))
END

[Martins Vanags -eazyBI-]

Hi @Dhivakar C 

I see you have a validation at the beginning of the code:

([Time].CurrentHierarchyMember.Level.Name = "Day"
and
NOT CoalesceEmpty([Time].CurrentHierarchyMember.Get("Week day name"),"") MATCHES "Saturday|Sunday")

It would not calculate results for Saturtday in your current formula because of that line.

Also, please select Saturtday as working day in Time options since you are using the DateDiffWorkdays function later in the formula.

https://docs.eazybi.com/eazybi/data-import/data-from-jira#DatafromJira-Timedimension

Martins / eazyBI

[Fabian Lim]

Hi @Dhivakar C

You can also try the import settings where you calculate what working days are: https://docs.eazybi.com/eazybi/data-import/data-from-jira