Latest date data

[Kavya123]

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

[Anonymous · Accepted solution]

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.

[Kavya123]

Thanks a lot. It is working.