I am trying to expand on a previous post (here) where I take my shift workers and list them out. But instead of just counting the number of shifts worked, I need to display the date from a custom date field from the jira ticket (Start Date and/or Event Date) and include the most current date from across a few different ticket types (Green Ticket, Blue Ticket, Red Ticket)
It doesn’t need to be in two separate columns, (Names/Date), it can be Name/Green Ticket Date/Red Ticket/Blue Ticket Date.
In this scenario, you should build a calcauted measure in Measures. For the calculation, iterate through all issues related to the shift worker. For this calcaution, use function Descendants() to go through all issues, function Max() to find the issue with the latest date among those.
The expression to find the latest date among issues might look like this:
--convert timestamp to date format
TimestampToDate(
--find the latest date among issues in the set
Max(
Filter(
--iterate through individual issues
Descendants([Issue].CurrentMember,[Issue].[Issue]),
NOT IsEmpty([Measures].[Issue Start Date or Event Date])
),
--if issue is related to the Shift, get the date for comparison
CASE WHEN
--use the calculated measures to see the related issue for the Shift
[Measures].[1st Shift count] > 0
THEN --convert date to timestamp to compare with other dates
DateToTimestamp([Measures].[Issue Start Date or Event Date])
END
)
)
Set measure formatting to Date. More details on calculated measures and formatting are here: Calculated measures and members
In the report, choose to drill into the new measure by dimension Issue Type to see the latest date for each issue type—more details here: Create reports.
This works really well, however it won’t show the date. I do have a few questions beside that:
When you say “iterate”, if I have multiple ticket types do I just repeat the Descendents line?
For one ticket type, all people can be scheduled for one of three shifts (not just first), so I need to be able to look at any shift on this one particular ticket type. Would I just repeat the [Measures].[1st Shift Count] > 0 with 2nd and 3rd?
the [measures].[issue start date or event date] line should work with a custom named date field, right? ([measures].[issues with custom name date]) *assuming all import settings are correct
In both locations where [measures].[issue start date or event date] is, do I select both the same (i.e. both “start date” or “event date”) or if I put “Start Date” first, “Event Date” is the second one (or vice versa)