Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Eazybi reporting

Dhivakar C
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
October 20, 2021

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

Comment

Log in or Sign up to comment
Mārtiņš Vanags
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 21, 2021

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
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 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

Like Alessandro Lombardo likes this
TAGS
AUG Leaders

Atlassian Community Events