Dynamic max value by date based on data slicer grouped by cayegory

[lorentz]

Hello everyone,

i encounter some difficulties to achieve a particular requirment.

the dataset is the following

the requirement is to obtain the State related to max Date for each Object

i achieved this using this formula:

max_state =

var max_date =CALCULATE (

    MAX ( Foglio1[Date]),

    ALLEXCEPT ( Foglio1,Foglio1[Object])

)

return calculate(values(Foglio1[State]),filter(Foglio1,Foglio1[Date]=max_date))

and now i have two problems:

1)  the formula does not respond to Date slicer, by moving slicer to 19/06/2022, in order to achieve this result:

2) i have a measure, but what i really want is to obtain a dynamic column with caldulated State related on max Date for each Obeject, changing moving data slicer.

I need this in order to use it in a Donut chart obtaining something like this:

 

I've been stuck on this for days, so will appreciate any kind of help or suggestions, thanks a lot.

[lbendlin · Accepted solution]

your date slicers need to be fed by an unconnected table.  I also recommend you rethink the "between" setting. A status is a momentary thing. The status for 1a on 19/06/2022 was Modified, not closed.

see attached