Finding the last value of a column depending on the selected date

[Martinator007]

Dear all.

I have a problem and did not find something similar.

I have a table with IDs:

ID	State	Date
223	20	06.05.2018
223	24	08.05.2018
223	26	11.05.2018
223	66	12.05.2018
225	20	07.05.2018
225	24	09.05.2018
225	26	14.05.2018
225	66	15.05.2018
301	20	09.05.2018
301	24	10.05.2018
301	26	12.05.2018
301	31	13.05.2018
301	66	15.05.2018

What I want to have is that I can select a date (e.q. via slicer) and for each ID I get the state (for that specific date or before). That means, selecting the date 13.05.2019 should result in:

ID	State
223	66
225	24
301	31

Is there any possiblity to realize that?

Thanks a lot in advance and

warm Regards

Martin

[Zubair_Muhammad · Accepted solution]

Hi @Martinator007 

Create a calendar table. Then we can use this formula

Please see attached file as well

Measure =
MAXX (
    TOPN (
        1,
        FILTER ( table1, [Date] <= SELECTEDVALUE ( 'Calendar'[Date] ) ),
        Table1[Date], DESC
    ),
    [State]
)

[Martinator007]

Hi Zubair_Muhammad.

Thanks a lot for your fast solution !! That works perfectly.

May one additional question. The table works perfect. It is also possible to use that measure in a diagramm.

In that example I would like to have for a certain date the number of worflow IDs depending on the measure.

I did try that but I cannot summarize the measure. I thinkt that is possible but that topic is really new for me.

Best regards,

Martin