Return next milestone based on today date

[bradleymac11]

Hi all,

I have tried to search for other solutions which would solve my question but havnt seemed to be able to find the right one and currently feel out of depth

My data is currently structured:

Project:	Stage:	Date:
NSW	Plan	1/05/2019
NSW	Execute	1/01/2020
NSW	close	1/01/2021
Vic	Plan	1/05/2017
Vic	Execute	1/01/2018
VIC	close	1/01/2019

For reporting, per project we were wishing to identify the current stage based on todays date, as well as the next stage to occur. Was unsure to takle this by measure or column.

Where no stage was to come after todays date was wishing to return the last stage.
Any help would be appreciated

[harshnathani]

Hi @bradleymac11 ,

You can create 2 measures

Next Stage =

var _a = MAX('Table'[Date])

var _NextDate = CALCULATE(MIN('Table'[Date]), FILTER(ALLEXCEPT('Table','Table'[Project]), 'Table'[Date] > _a))

RETURN

//_NextDate

CALCULATE(MAX('Table'[Stage]), FILTER(ALLEXCEPT('Table','Table'[Project]), 'Table'[Date] = _NextDate))

Current Stage =

var _today = Today()

var _a = MAX('Table'[Date])

var _NextDate = CALCULATE(MIN('Table'[Date]), FILTER(ALLEXCEPT('Table','Table'[Project]), 'Table'[Date] > _a))

RETURN

//_NextDate

IF(DATEDIFF (_NextDate,_today,DAY) < 0 && DATEDIFF (MAX('Table'[Date]),_today,DAY) > 0, "Current State")

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)