Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
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!
@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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |