Try to use result of measure in column diagram

[Martinator007]

Dear PowerBi experts.

I am new in this field but yesterday I had great help from the community to solve an issue.

I have the following simplified table:

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
405	20	12.05.2018
405	26	13.05.2018
405	31	16.05.2018
405	66	18.05.2018

I need the last state depending on a date (date is selected via slicer).  In my example the selection of 14.05.2018 should result in the following table:

ID	State
223	66
225	26
301	31
405	26

Zubair_Muhammad helped me to create the solution:
https://community.powerbi.com/t5/Desktop/Finding-the-last-value-of-a-column-depending-on-the-selecte...

and i have exact the table I need by creating a date-column and using the following Measure: 

Measure = MAXX(TOPN(1;Filter(table1;[Date]<=selectedvalue('Calendar'[Date]));Table1[Date];Desc);[State])

Now I tried to get a column diagram in that manner:

State	Number
26	2
31	1
66	1

But I find no solution to get the summarized number for each state depending on the date (14.05.2018).

I would be very happy if that would be possible.

Thanks and best regards,
martin

[Martinator007]

Not sure if it is complex or I did not describe my issue on a good way. Maybe someone has an idea?

[v-danhe-msft]

Hi @Martinator007 ,

I could not understand why the number of state 26 is 2 due to the state of ID 225 should be 24(date of 26 is 5.14) right?

State	Number
26	2
31	1
66	1

Regards,

Daniel He

[Martinator007]

Hi Daniel

You are fully right! Thanks for your notice I did change the date to 5.14.

Warm regards,

Martin