Error when display value for maximum year but the value shows other year too

[Anonymous]

Hello experts,

I want to display the value for maximum year.

For example, the maximum year will be the latest year which is 2022, so the values should only display data for year 2022. But there are few values from year 2020 also have been displayed which is not correct.

My formula looks like below.. Need your help & insights on this..

var latestYear = YEAR(MAX('table'[date]))

var valueX =CALCULATE(SUM('table'[Value])/1000, table[category]= "pink", 

YEAR('table'[date])=latestYear)

return

valueX

Thanks in advance.

Izzaty

[Anonymous]

Hi @Anonymous ,

You can try this code to create a measure.

Value in Max Year =
VAR _latestYear =
    YEAR ( MAXX ( ALL ( 'table' ), 'table'[date] ) )
VAR _valueX =
    CALCULATE (
        SUM ( 'table'[Value] ) / 1000,
        FILTER (
            'table',
            AND ( table[category] = "pink", YEAR ( 'table'[date] ) = _latestYear )
        )
    )
RETURN
    _valueX

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.

[amitchandak]

@Anonymous , Try a measure like (With help from a separate date table

var latestYear = YEAR(MAX('table'[date]))

CALCULATE(SUM('table'[Value])/1000, table[category]= "pink",
filter(all('Date') , YEAR('Date'[date])=latestYear) )

refer

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA