Help with Calculated Column

[mithunt]

Hello All,

I am looking for some assistance in finding a calculated column which gives me results as specified in column Sales at Last date(Previous Month) 

I have used this DAX and it pulls the latest value instead of last value from last month date

pms = CALCULATE(MAXX('Data','Data'[Sales]), 'Data'[Product]=EARLIER('Data'[Product]),'Data'[Last Date of Month],ALLEXCEPT('Data','Data'[Date]))

Here is the format which I am looking for

Date	Product	Sales	Last date of Month	Sales at Last date(Previous Month)
3/22/2022	Cycle	185	2/28/2022
3/23/2022	Cycle	118	2/28/2022
3/24/2022	Cycle	153	2/28/2022
3/25/2022	Cycle	103	2/28/2022
3/26/2022	Cycle	145	2/28/2022
3/27/2022	Cycle	189	2/28/2022
3/28/2022	Cycle	183	2/28/2022
3/29/2022	Cycle	115	2/28/2022
3/30/2022	Cycle	194	2/28/2022
3/31/2022	Cycle	105	2/28/2022
4/1/2022	Cycle	197	3/31/2022	105
4/2/2022	Cycle	190	3/31/2022	105
4/3/2022	Cycle	105	3/31/2022	105
4/4/2022	Cycle	119	3/31/2022	105
4/5/2022	Cycle	152	3/31/2022	105
4/6/2022	Cycle	156	3/31/2022	105
4/7/2022	Cycle	134	3/31/2022	105
4/8/2022	Cycle	126	3/31/2022	105
4/9/2022	Cycle	144	3/31/2022	105
4/10/2022	Cycle	195	3/31/2022	105
4/11/2022	Cycle	141	3/31/2022	105
4/12/2022	Cycle	169	3/31/2022	105
4/13/2022	Cycle	127	3/31/2022	105
4/14/2022	Cycle	174	3/31/2022	105
4/15/2022	Cycle	120	3/31/2022	105
4/16/2022	Cycle	156	3/31/2022	105
4/17/2022	Cycle	120	3/31/2022	105
4/18/2022	Cycle	111	3/31/2022	105
4/19/2022	Cycle	164	3/31/2022	105
4/20/2022	Cycle	160	3/31/2022	105
4/21/2022	Cycle	148	3/31/2022	105
4/22/2022	Cycle	109	3/31/2022	105
4/23/2022	Cycle	147	3/31/2022	105
4/24/2022	Cycle	118	3/31/2022	105
4/25/2022	Cycle	161	3/31/2022	105
4/26/2022	Cycle	181	3/31/2022	105
4/27/2022	Cycle	122	3/31/2022	105
4/28/2022	Cycle	189	3/31/2022	105
4/29/2022	Cycle	174	3/31/2022	105
4/30/2022	Cycle	157	3/31/2022	105
5/1/2022	Cycle	171	4/30/2022	157
5/2/2022	Cycle	142	4/30/2022	157
5/3/2022	Cycle	174	4/30/2022	157
5/4/2022	Cycle	196	4/30/2022	157
5/5/2022	Cycle	119	4/30/2022	157
5/6/2022	Cycle	188	4/30/2022	157
5/7/2022	Cycle	185	4/30/2022	157
5/8/2022	Cycle	116	4/30/2022	157
5/9/2022	Cycle	187	4/30/2022	157
5/10/2022	Cycle	160	4/30/2022	157
5/11/2022	Cycle	142	4/30/2022	157

Thanks all in advance and looking for some assistance here

[daXtreme · Accepted solution]

pms = // calc column
var LastDateOfMonth = 'Data'[Last date of month]
var CurrentProduct = 'Data'[Product]
var Result =
    MAXX(
        filter(
            'Data',
            'Data'[Date] = LastDateOfMonth
            &&
            'Data'[Product] = CurrentProduct
        ),
        'Data'[Sales]
    )
return
    Result

[mithunt]

Thank you so very much it worked like a charm.. 

need to get some experience on writing loops then..