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 Financial product in calendar year.  I looking to create column for it.  

 

For Example,  if customer has aquired Financial product on 15,Feb 2021 then Code check  last 360 days to find if he had NF product.  If customer had NF product then mark it "Y".

 

I am badly stuck in writng DAX formula, so  would be grateful for any  suggestions. 

Chana

 

IDProduct    Financial Product Aquired (Calendar Year )      Transaction Date        Conversion (NF-> F)
1NFN5-May-20 
1FY6-Feb-21Y
2NFN3-Apr-20 
2NFN1-Jan-21 
3NFN2-Jan-20 
3FY4-Sep-21 
4NFN25-Nov-20 
4FY14-Jul-21Y

 

 

 

 

</p body>

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

     



7 Replies

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