Help with Calculated Column

Question

Hello All,&nbsp;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)&nbsp;&nbsp;I have used this DAX and it pulls the latest value instead of last value from last month date&nbsp;pms = CALCULATE(MAXX('Data','Data'[Sales]), 'Data'[Product]=EARLIER('Data'[Product]),'Data'[Last Date of Month],ALLEXCEPT('Data','Data'[Date]))&nbsp;&nbsp;Here is the format which I am looking for&nbsp;DateProductSalesLast date of MonthSales at Last date(Previous Month)3/22/2022Cycle1852/28/2022&nbsp;3/23/2022Cycle1182/28/2022&nbsp;3/24/2022Cycle1532/28/2022&nbsp;3/25/2022Cycle1032/28/2022&nbsp;3/26/2022Cycle1452/28/2022&nbsp;3/27/2022Cycle1892/28/2022&nbsp;3/28/2022Cycle1832/28/2022&nbsp;3/29/2022Cycle1152/28/2022&nbsp;3/30/2022Cycle1942/28/2022&nbsp;3/31/2022Cycle1052/28/2022&nbsp;4/1/2022Cycle1973/31/20221054/2/2022Cycle1903/31/20221054/3/2022Cycle1053/31/20221054/4/2022Cycle1193/31/20221054/5/2022Cycle1523/31/20221054/6/2022Cycle1563/31/20221054/7/2022Cycle1343/31/20221054/8/2022Cycle1263/31/20221054/9/2022Cycle1443/31/20221054/10/2022Cycle1953/31/20221054/11/2022Cycle1413/31/20221054/12/2022Cycle1693/31/20221054/13/2022Cycle1273/31/20221054/14/2022Cycle1743/31/20221054/15/2022Cycle1203/31/20221054/16/2022Cycle1563/31/20221054/17/2022Cycle1203/31/20221054/18/2022Cycle1113/31/20221054/19/2022Cycle1643/31/20221054/20/2022Cycle1603/31/20221054/21/2022Cycle1483/31/20221054/22/2022Cycle1093/31/20221054/23/2022Cycle1473/31/20221054/24/2022Cycle1183/31/20221054/25/2022Cycle1613/31/20221054/26/2022Cycle1813/31/20221054/27/2022Cycle1223/31/20221054/28/2022Cycle1893/31/20221054/29/2022Cycle1743/31/20221054/30/2022Cycle1573/31/20221055/1/2022Cycle1714/30/20221575/2/2022Cycle1424/30/20221575/3/2022Cycle1744/30/20221575/4/2022Cycle1964/30/20221575/5/2022Cycle1194/30/20221575/6/2022Cycle1884/30/20221575/7/2022Cycle1854/30/20221575/8/2022Cycle1164/30/20221575/9/2022Cycle1874/30/20221575/10/2022Cycle1604/30/20221575/11/2022Cycle1424/30/2022157&nbsp;Thanks all in advance and looking for some assistance here

daxtreme · Accepted Answer

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

Forum Discussion

Help with Calculated Column

2 Replies

Related content

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

Power Query or Calculated columns

IF calculated column

Dynamic Variable in Calculated Columns

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