Forum Discussion

jaltoft's avatar
jaltoft
Resolver I
5 years ago

Countifs replicate Excel

=IF([This Week''s Contacts]="","",IF(COUNTIFS($G:$G,[Cust No],$T:$T,"<"&[Start Date],$T:$T,">="&EOMONTH([Start Date],-12)+1)>=1,"Y",""))

 

Column G = Cust number

Column T = Start date

 

Hello,

I want to replicate the above Excel formulae within a a DAX calculated column  within my Power BI model can anyone please help?

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    jaltoft , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • jaltoft's avatar
      jaltoft
      Resolver I
      5 years ago

      Hello amitchandak 

       

      This is an example of what the data would look like and I want to replicate the column Contact in 12 months -

       

      So the forumlae in excel I need to replicate in a DAX calculated column is =IF(A2="","",IF(COUNTIFS($B$2:$B$12,B2,$C$2:$C$12,"<"&C2,$C$2:$C$12,">="&EOMONTH(C2,-12)+1)>=1,"Y",""))

       

      The columns are A-D in this version

      This Weeks ContactsClient NoStart DateContact 12 Months
      Y123415/10/2020Y
      Y123315/10/2020Y
       111112/10/2020 
      Y188815/10/2020Y
      Y895212/10/2020 
      Y253511/10/2020 
      Y123410/09/2020 
      Y123308/08/2020 
      Y111107/08/2020 
      Y188806/06/2020 
      Y925501/06/2019 
    • jaltoft's avatar
      jaltoft
      Resolver I
      5 years ago

      amitchandak  essentially what I need to do is mark Y if the customer has previously been contacted in the last 12 months.