Finding Conversion in last 360 days -

[Anonymous]

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

ID	Product	Financial Product Aquired (Calendar Year )	Transaction Date	Conversion (NF-> F)
1	NF	N	5-May-20
1	F	Y	6-Feb-21	Y
2	NF	N	3-Apr-20
2	NF	N	1-Jan-21
3	NF	N	2-Jan-20
3	F	Y	4-Sep-21
4	NF	N	25-Nov-20
4	F	Y	14-Jul-21	Y

</p body>

[ValtteriN]

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]

@ValtteriN  Thankyou so mcuh for code. 

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

[ValtteriN]

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]

@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]

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]

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 .  

[ValtteriN · Accepted solution]

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