Measure to determine if item is within 5 year / 10 year periods

[Googlecanthelp]

Hi all, 

Struggling with some date work based on years against TODAY and a static date. I have never used calendars and Im wondering if this is what I need. 

I have a table of components which have a column called PRIORTY DATE, this column has both past and future dates, I want to concentrate on the past dates. The components are considered as overdue if the PRIOROTY DATE is before TODAY, however I need to be able to determine HOW overdue they are and split them into one of 3 categories. 

Overdue within 5 years (from TODAY)

Overdue between 5 years & 10 years (from TODAY)

Overdue over 10 years (from TODAY).

Example data 

One is under 5 years overdue 

One is between 5 & 6 years overdue 

One is over 10 years overdue 

component_ref	priority_date	Overdue_Current
573384	12/10/1996	Overdue
260108	19/02/2016	Overdue
260326	19/02/2018	Overdue

Any suggestions on either a column or a measure most welcome 😁

Thanks! 

[Anonymous · Accepted solution]

@Googlecanthelp 

Please find the solution below using measure:

Dax Measure:

OverdueCurent =

VAR datedifference =

DATEDIFF ( SELECTEDVALUE ( Data[priority_date] ), TODAY (), YEAR )

VAR diff =

IF (

datedifference <= 5,

"Under 5 years",

IF (

datedifference > 5

&& datedifference <= 10,

"Between 5-10 years",

IF ( datedifference > 10, "Over 10 years", BLANK () )

)

)

RETURN

diff

Please accept it as a solution if it matches your requirement🙂

[v-jayw-msft · Accepted solution]

Hi @Googlecanthelp ,

Please check the measure.

Measure = 
var _5year = EDATE(TODAY(),-60)
var _10year = EDATE(TODAY(),-120)
var _prio = SELECTEDVALUE('Table'[priority_date])
return
SWITCH(TRUE(),
_prio<TODAY()&&_prio>=_5year,"Overdue within 5 years",
_prio<_5year&&_prio>=_10year,"Overdue between 5 years & 10 years",
_prio<_10year,"Overdue over 10 years")

Best Regards,

Jay

[Anonymous · Accepted solution]

@Googlecanthelp 

Please find the solution below using measure:

Dax Measure:

OverdueCurent =

VAR datedifference =

DATEDIFF ( SELECTEDVALUE ( Data[priority_date] ), TODAY (), YEAR )

VAR diff =

IF (

datedifference <= 5,

"Under 5 years",

IF (

datedifference > 5

&& datedifference <= 10,

"Between 5-10 years",

IF ( datedifference > 10, "Over 10 years", BLANK () )

)

)

RETURN

diff

Please accept it as a solution if it matches your requirement🙂

[Googlecanthelp]

Thank you @Anonymous this worked a treat 🙂 

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

One of ways to solve this is to create a helper table, like Overdue Table in a model, and create a measure.

Overdue_current: =
IF (
HASONEVALUE ( Data[component_ref] ),
CALCULATE (
VALUES ( Overdue[Overdue] ),
FILTER (
Overdue,
( TODAY () - MAX ( Data[priority_date] ) ) / 365.25 >= Overdue[Min]
&& ( TODAY () - MAX ( Data[priority_date] ) ) / 365.25 < Overdue[Max]
)
)
)