Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Finding Conversion in last 360 days -

Hello Communnity,  Happy new year !   Can you pplease help me writing this DAX formula :   I've  to find conversion from  NF customers to Fin Customer in last 360 days, when customer has aquired...
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Anonymous 
    This seems to work:

    NF-F (column) =
    var _edate = NF_conversion[Transaction Date ]
    var _sdate = _edate-360
    var _customer = NF_conversion[ID]
    return
    IF(and(
    NF_conversion[Product]="F"

    &&NF_conversion[Financial Product Aquired (Calendar Year ) ]="Y",

    countrows(FILTER(all(NF_conversion),NF_conversion[Product]="NF"
    &&NF_conversion[Transaction Date ]>=_sdate
    &&NF_conversion[Transaction Date ]<=_edate
    &&_customer=NF_conversion[ID]))>=1),"Y","")

     



Anonymous's avatar
Anonymous
Not applicable
4 years ago

ValtteriN  Thankyou so mcuh for code. 

 

I need it in column as I have to further use it to filter data. 

 

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

Hi,

Here is the column version:

NF-F (column) =
var _edate = NF_conversion[Transaction Date ]
var _sdate = _edate-360
var _customer = NF_conversion[ID]
return
IF(and(
NF_conversion[Product]="F",

countrows(FILTER(all(NF_conversion),NF_conversion[Product]="NF"
&&NF_conversion[Transaction Date ]>=_sdate
&&NF_conversion[Transaction Date ]<=_edate
&&_customer=NF_conversion[ID]))>=1),"Y","")

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    ValtteriN 

    I have to apply Financial Product Aquired  filter  in the code , so that it only gives conversion for the customers who had aquired Financial products and in the past (360 days )  they had NF products. Can suggest where to apply that filter in code . Thnanks

     

    • ValtteriN's avatar
      ValtteriN
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Here should suffice:

      NF-F (column) =
      var _edate = NF_conversion[Transaction Date ]
      var _sdate = _edate-360
      var _customer = NF_conversion[ID]
      return
      IF(and(
      NF_conversion[Product]="F",

       

      countrows(FILTER(all(NF_conversion),NF_conversion[Product]="NF"
      &&NF_conversion[Transaction Date ]>=_sdate
      &&NF_conversion[Transaction Date ]<=_edate
      &&NF_conversion[ProductAquired]="Y"
      &&_customer=NF_conversion[ID]))>=1),"Y","")
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

         

        Hi ValtteriN ,  this is not working on my end can you please  suggest. I have added filter product acquired  is Y.  As I need conversion for only those that have actually aquired the Financial product only .