Forum Discussion
Dax help with conversion
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.
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!
2 Replies
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())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!
