Value of highest duration selected

[S3]

Date		Pieces		Price
02.01.22		490		1470
10.01.22		480		1440
31.01.22		546		1638
01.02.22		530		1590
10.02.22		550		1650
28.02.22		570		1710
31.03.22		543		1629
30.04.22		552		1656
31.05.22		576		1728

Date		MonthNo
02.01.22		202201
10.01.22		202201
31.01.22		202201
01.02.22		202202
10.02.22		202202
28.02.22		202202
31.03.22		202203
30.04.22		202204
31.05.22		202205

Hello, 

I have a dateset which shows the whole amount of Pieces each day, and not the difference, same goes for their price. 

I would first like to calculate the amount of pieces & prices at the end of each month. I have created this measure for each, but I don't think that's the optimal solution, even if it gives the wanted results:

Pieces in Month =

CALCULATE (

[Pieces],

FILTER('Dates',

(FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.01.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "28.02.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.03.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.04.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.05.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.06.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.07.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.08.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.09.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.10.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.11.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.12.22")))

Second, I would like to ask if it's possible to make it even more optimal and choose the maximum date value selected, even if it's in the middle of the month. 

Third, I would like to divide the number of pieces by the price in the maximum date selected. 

Is any of that possible? Thank you a lot. 

[Greg_Deckler]

@S3 First, I would recommend that you change your Date columns to be Date/Time data type instead of what appears to be text. That would eliminate the FORMAT functions. You can get the maximum date in context by just using MAX('Dates'[Date]). If you have that, then you can lookup the corresponding price using something like:

VAR __MaxDate  = MAX('Dates'[Date])

VAR __MaxPriceDate = MAXX(FILTER(ALL('Table'),[Date] <= __MaxDate),[Date])

VAR __Price = MAXX(FILTER(ALL('Table'),[Date] = __MaxPriceDate),[Price])

[S3]

Hello, thanks for the solution and the advice.

I do have the Date/Time, I jsut don't know why it wasn't working without the FORMAT with me 😕 even though in other measures it worked, when I was filtering quarters. 

As for your suggetsion above, I keep on getting this error message: 

The syntax for ')' is incorrect. (DAX(VAR __MaxDate = MAX('Dates'[Date])VAR __MaxPriceDate = MAXX(FILTER(ALL('Subscription by Product Daily UK Ja...etc

I copied and pasted it like it's written here. Thanks again.