Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Get next date in a list

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 dat...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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. :smileyhappy:

 

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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?