It is possible to do a chart with absence day per sprint using absence tracker and eazyBI?

[Francho]

Hello, 

I am trying to search some tool much better than excel because it is very hard to maintain it. I have found "Absence Tracker" to control the absence of users during a sprint and "EazyBI" to create charts easily.

The problem is that I am new using these tools and I have not idea about how can I get the percentage of time that each user of the team has been available during the sprint. For example, for me 100% means that the user has been available the 10 days of sprint. But if the user has been 90% then it means that the user has had one day off (because the sprint have 10 working days)
Is it possible to get this kind of info to do after that a chart to see the percentage of availability per user?

Any help will be welcome!! 

Thank you very much for your time.

[Gerda Grantiņa]

Hi @Francho

Welcome to Atlassian Community! 
In eazyBI, you could use Sprint dimension in Rows and create a calculated measure in Measure dimension that checks if any issues are created from "Absence Tracker" project with start or end dates between Sprint start and end dates.
You can use this formula:

-- annotations.drill_through_non_empty = false
Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
(DateBetween(
[Measures].[Issue Start Date],
[Measures].[Sprint start date], 
[Measures].[Sprint end date]
) 
OR 
DateBetween(
[Measures].[Issue End Date],
[Measures].[Sprint start date], 
[Measures].[Sprint end date]
)) 
AND
(([Measures].[Issues with Start Date],
[Project].[Absence Tracker],
[Sprint].DefaultMember)>0
OR 
([Measures].[Issues with End Date],
[Project].[Absence Tracker],
[Sprint].DefaultMember)>0)
),
DateDiffWorkdays(
IIf(
DateCompare([Measures].[Issue Start Date], [Measures].[Sprint start date])>0,
[Measures].[Issue Start Date],
[Measures].[Sprint start date]
),
IIf(
DateCompare([Measures].[Issue End Date], [Measures].[Sprint end date])>0,
[Measures].[Sprint end date],
[Measures].[Issue End Date]
)
)
)

Then you need to calculate Sprint duration using this formula:

DateDiffWorkdays(
DateWithoutTime([Measures].[Sprint start date]),
DateWithoutTime([Measures].[Sprint end date])
)

And then use it to get the percentage of availability:

CASE WHEN
[Measures].[Sprint duration]>0
THEN
1 - CoalesceEmpty([Measures].[Absence days],0)/
[Measures].[Sprint duration]
END

Then drill into measures "Absence days" and availability by Assignee dimension: https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Drillintomeasurebyanotherdimensionlevel

In the report all together would look like this:



Best,
Gerda // support@eazyBI.com