Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,299,645
Community Members
 
Community Events
165
Community Groups

Eazybi reporting

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

2 comments

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 Community Leader Oct 23, 2021

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

Comment

Log in or Sign up to comment
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you