How to build a calculated measure to pull in the closest upcoming date

Hi,

I am currently building a project management report. In line with this I am looking to know when the project manager is available to take on their next assignment. I have a custom field for the target completion dates and looking to use the assignee field for the project manger. How would I go about a query that allows me to see the nearest project completion date so this can be used as the anchor for availability?

For example lets say the project completion dates were November 11th 2021, December 25th 2021, and January 1st, 2022 for three separate projects under the same manager. I would be looking for the query to show me the November date as it will be the closest project to be completed.

Thank you in advance.

Hi @dcachia

Welcome to eazyBI community.
Am I right to assume that you have one issue per each project and this issue has type “Project” where you assign the right person as manager and set target completion date?

if that is so, you could first make sure that “target completion date” is selected in the import options page and imported as property and measure.
Then create new calculated measure using the following formula where you find the min closes from completion dates.

Order(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issue type] = "Project"
AND
DateBetween([Measures].[Issue target completion],"today","2 years from now")
AND
(
[Measures].[Issues with target completion date],
[Issue Type].[Project]
)>0
),
DateToTimestamp([Measures].[Issue Target completion date]), 
BASC
).item(0).Get('Target completion date')

it will always check the dates for next 2 years.

When selecting this calculated measure in your report, make sure you enable “Nonempty” cross join to optimize the report query.
And don’t forget to save the calculated measure in the correct format.

Martins / eazyBI

Hello @martins.vanags!

Thank you for the formula breakdown here, this is exactly what I was looking for. I greatly appreciate the help!