topic Re: Latest date data in Developer

Latest date data

Kavya123 — Mon, 21 Feb 2022 10:18:56 GMT

Hi Everyone,

I need to create a column display the latest values based on the year selection.

For example:

If I select code "BEVH" and year 2015 it should display the value 94.

If I select code "BEVH" and select both the years 2015 and 2016 then it should display the value 95 (latest date data based on multi year selection)

Code	Year(MM/DD/YYYY)	FIValue
BEVH	4/1/2014	93
BEVH	6/1/2015	94
BEVH	1/1/2015	98
BEVH	1/1/2016	95
BWH	1/1/2014	86
BWH	6/1/2016	98.2
BWH	4/1/2016	96.3
CATH	4/1/2013	80
CATH	6/1/2015	87

Thanks in advance

Re: Latest date data

Anonymous — Thu, 24 Feb 2022 03:04:08 GMT

Hi @Kavya123 ,

I suggest you to create an unrelated Year table for slicer. Then create a measure to calculate the latest date data based on selection.

Year Selection = VAR _ADD = ADDCOLUMNS ( VALUES ( 'Table'[Year(MM/DD/YYYY)] ), "Year", YEAR ( [Year(MM/DD/YYYY)] ) ) VAR _SUMMARIZE = SUMMARIZE ( _ADD, [Year] ) RETURN _SUMMARIZE

Measure:

Latest date data = VAR _SELECT_YEAR = VALUES ( 'Year Selection'[Year] ) VAR _LAST_DAY = CALCULATE ( MAX ( 'Table'[Year(MM/DD/YYYY)] ), FILTER ( 'Table', YEAR ( 'Table'[Year(MM/DD/YYYY)] ) IN _SELECT_YEAR ) ) RETURN CALCULATE ( SUM ( 'Table'[FIValue] ), FILTER ( 'Table', 'Table'[Year(MM/DD/YYYY)] = _LAST_DAY ) )

Result is as below.

If you select code "BEVH" and year 2015 it should display the value 94.

If you select code "BEVH" and select both the years 2015 and 2016 then it should display the value 95

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Latest date data

Kavya123 — Thu, 24 Feb 2022 12:23:38 GMT

Thanks a lot. It is working.