Dax 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. 

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

Please try this Measure or Calculated column.

1 Measure

IsNF -> F =

VAR PreDate =

    CALCULATE (

        MAX ( 'Table'[Date] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[ID] = MAX ( 'Table'[ID] )

                && 'Table'[Date] < MAX ( 'Table'[Date] )

                && [Product F Acquired] = "N"

        )

    )

RETURN

    IF (

        ISBLANK(PreDate),

        "",

        IF ( DATEDIFF ( PreDate, MAX ( 'Table'[Date] ), DAY ) < 365, "Y", "N" )

    )

2 Calculated column

IsNF -> F_Col =

VAR PreDate =

    CALCULATE (

        MAX ( 'Table'[Date] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[ID] = EARLIER ( 'Table'[ID] )

                && 'Table'[Date] < EARLIER ( 'Table'[Date] )

                && [Product F Acquired] = "N"

        )

    )

RETURN

    IF (

        ISBLANK ( PreDate ),

        "",

        IF ( DATEDIFF ( PreDate, 'Table'[Date], DAY ) < 365, "Y", "N" )

    )

Then, the result should look like this.

Attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak]

@Anonymous , A new column like

new column =
var _date1 = [Date]
var _date2 = date(year(_date1), month(_date1)-12, day(_date1))
var _cnt =countx(filter(Table, [Date]>= _date2 && [Date] <= _date1 && [Id] = earlier([ID]) && [Product Name] = "F"), [Product Name])
return
if( [Product Name] = "NF" && not(isblank(_cnt)) , "Y" , blank())