Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Previous month calculation

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 

 

MonthAgreementIs Agreement New?
12/1/2020101
12/1/2020111
12/1/2020291
12/1/2020211
12/1/2020201
12/1/2020111
1/1/2021110
1/1/2021401
1/1/2021290

 

Thanks...

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    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)

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Anonymous 

    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)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Ryan_mayu

       

      Great solution.  It solved what we were looking for.

       

      Regards,

       

      Albin

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

     

    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)

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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.