Need help with conversion

[Anonymous]

Hi community, 

Can you please help me writing following in measure and Column. 

I have product  two products in data ( F and NF) . I  want create measure and column where I could identify customer has converted from NF to F.  I have two years of data and cut of time to look NF product  is 365 days. that means if customer has acquired  F  product  then code check  product NF in last one years  of product F acquired  date.   

ID | Product Name  |  Date                     |    Product F Aquired |   NF -> F

1       NF                     2020/Sept/ 2               N

1       F                        2021/Jan/1                  Y                                   Y

2      NF                      2020/Feb/2020            N

2      F                         2021/Dec /2021          Y                                   N

3      NF                      20/ March/2021          N                                       

3       F                      20/July/2021               Y                                     Y

Thanks,

Chans.

[Anonymous · Accepted solution]

HI @Anonymous,

Did you mean the condition are based on the two products that were stored in two different years? If that is the case, you can try to use the following calculated column formulas:

NF to F = 
IF (
    'Table'[Product] = "F",
    VAR prevNFDate =
        CALCULATE (
            MAX ( 'Table'[Date] ),
            FILTER (
                'Table',
                [ID] = EARLIER ( 'Table'[ID] )
                    && [Date] < EARLIER ( 'Table'[Date] )
                    && 'Table'[Product] = "NF"
            )
        )
    RETURN
        IF ( YEAR ( 'Table'[Date] ) - YEAR ( prevNFDate ) >= 1, "Y", "N" )
)

Regards,

Xiaoxin Sheng

[VahidDM]

Hi @Anonymous 

Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/