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","")

     



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

Hi,

Here is one way to do this with a measure:

NF-F =
var _edate = MAX(NF_conversion[Transaction Date ])
var _sdate = _edate-360
var _customer = MAX(NF_conversion[ID])
return
IF(and(
MAX(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","")
 
 
End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!


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","")