topic Re: Finding Conversion in last 360 days - in DAX Commands and Tips

Finding Conversion in last 360 days -

Anonymous — Mon, 17 Jan 2022 17:21:09 GMT

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>

Re: Finding Conversion in last 360 days -

ValtteriN — Mon, 17 Jan 2022 17:39:48 GMT

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!

Re: Finding Conversion in last 360 days -

Anonymous — Mon, 17 Jan 2022 17:54:34 GMT

ValtteriN Thankyou so mcuh for code.

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

Re: Finding Conversion in last 360 days -

ValtteriN — Mon, 17 Jan 2022 17:58:28 GMT

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

Re: Finding Conversion in last 360 days -

Anonymous — Mon, 17 Jan 2022 19:38:01 GMT

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

Re: Finding Conversion in last 360 days -

ValtteriN — Mon, 17 Jan 2022 19:42:12 GMT

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

Re: Finding Conversion in last 360 days -

Anonymous — Mon, 17 Jan 2022 21:21:21 GMT

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 .

Re: Finding Conversion in last 360 days -

ValtteriN — Tue, 18 Jan 2022 06:46:29 GMT

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