topic Measure to determine if item is within 5 year / 10 year periods in DAX Commands and Tips

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

Anonymous — Mon, 07 Mar 2022 10:48:51 GMT

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!

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

Jihwan_Kim — Mon, 07 Mar 2022 11:24:41 GMT

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]
)
)
)

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

Anonymous — Mon, 07 Mar 2022 13:56:44 GMT

Anonymous

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🙂

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

Anonymous — Thu, 10 Mar 2022 08:47:48 GMT

Hi Anonymous ,

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

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

Anonymous — Mon, 14 Mar 2022 11:51:46 GMT

Thank you Anonymous this worked a treat 🙂