Help with Calculated Column

mithunt — Sun, 22 May 2022 18:05:08 GMT

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

Re: Help with Calculated Column

daXtreme — Sun, 22 May 2022 18:36:30 GMT

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

Re: Help with Calculated Column

mithunt — Sun, 22 May 2022 19:45:20 GMT

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

need to get some experience on writing loops then..

topic Help with Calculated Column in DAX Commands and Tips

Help with Calculated Column

Re: Help with Calculated Column

Re: Help with Calculated Column