Get next date in a list

[Anonymous]

This question is two-fold:

I have a list of projects, each with several milestones and associated dates. See an example of one project below that has milestones ("Action Title") and milestone dates "Deadline"

Is there a way to create a function that returns the next date in a list of dates? Then, once you have that, is there a way to return the milestone that is associated with that date? For example in the data set above, is there a way to create two separate functions that:

a) Return 3.29.20 as the next date

b) Return "DG3" as next milestone

I'm trying to create a gantt chart using this data since Power BI doesn't currently support showing multiple milestones along a single project row. 

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you should be able to use the formulas below to create two measures to get next date and milestone for an Id.

Next Date = 
var currentDate = TODAY()
var currentID = MAX(Table1[Id])
return
CALCULATE(MIN(Table1[Deadline]),FILTER(ALL(Table1),Table1[Id]=currentID&&Table1[Deadline]>=TODAY()))

Next Milestone = 
var currentDate = TODAY()
var currentID = MAX(Table1[Id])
return
CALCULATE(MIN(Table1[Action Title]),FILTER(ALL(Table1),Table1[Id]=currentID&&Table1[Deadline]>=TODAY()))

Here is the same pbix file for your reference. 

Regards

[Anonymous]

thanks a lot - that worked!

Now for the tricky part - how can I show this in a gantt chart? When I try to calculate duration until milestone by just taking the difference between Next MS Date and TODAY(), the return value is in date format. Is there any way to get this into a number format so that I can use it as a duration in the gantt?