Previous month calculation

[achinchilla]

Hi Community,

I need to set a Column with a 1 or 0 if a value in a column is new in the Current Month (1) or if it is the same in previous month (0).  How can I do that?  I have tried many ways but I haven't been able to get to it.

Here is a sample 

Month	Agreement	Is Agreement New?
12/1/2020	10	1
12/1/2020	11	1
12/1/2020	29	1
12/1/2020	21	1
12/1/2020	20	1
12/1/2020	11	1
1/1/2021	11	0
1/1/2021	40	1
1/1/2021	29	0

Thanks...

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(ISNUMBER(CALCULATE(COUNTROWS(Data_2),FILTER(Data_2,Data_2[Agreement]=EARLIER(Data_2[Agreement])&&Data_2[Month]<EARLIER(Data_2[Month])))),0,1)

Hope this helps.

[Vera_33]

Hi @achinchilla 

You want a DAX calculated column or M? Here is one DAX column

Is Agreement New? = 
VAR PreMonth = DATEADD(AgreementTable[Month],-1,MONTH)
VAR CurAgreement = AgreementTable[Agreement]
VAR T1 = SELECTCOLUMNS(FILTER(ALL(AgreementTable),AgreementTable[Month]=PreMonth),"NEW",[Agreement])
RETURN
IF(CurAgreement IN T1,0,1)

[ryan_mayu · Accepted solution]

@achinchilla 

you can create a column

Column = if(ISBLANK(maxx(FILTER('Table','Table'[Agreement]=EARLIER('Table'[Agreement])&&'Table'[Month]<EARLIER('Table'[Month])),'Table'[Month])),1,0)

[achinchilla]

Hi Ryan_mayu

Great solution.  It solved what we were looking for.

Regards,

Albin