Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.