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,
I have a list of policy sales and i need to identify which customers have renewed. Each policy is 12 months in duration and each customer has an unique ID.
Can anyone help identify which policy have been renewed? I'd like to sum this data and the turn it into a percentage, which is the easy bit. I'm just struggling to write the measure to idenify renewed customers.
Sample data:
Solved! Go to Solution.
Try this measure:
Renewed =
VAR _ID =
MAX ( 'Table'[Unique ID] )
VAR _Reg =
MAX ( 'Table'[Reg] )
VAR _Renewed =
IF (
COUNTROWS (
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Unique ID] = _ID && 'Table'[Reg] = _Reg ),
'Table'[Unique ID],
'Table'[Reg],
'Table'[Inception Date]
)
) > 1,
"Renewed"
)
VAR _date =
CALCULATE (
MAX ( 'Table'[Inception Date] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Unique ID] ), NOT ISBLANK ( _Renewed ) )
)
RETURN
IF ( MAX ( 'Table'[Inception Date] ) = _date, _date )
Proud to be a Super User!
Paul on Linkedin.
Unique ID | Reg | Policy Count | Product | Inception Date | Is_renewal (1,0) | |||
ABC123 | NU72CAR | 1 | MOT Cover | 01/01/2021 | 0 | |||
ABC124 | NU72CAB | 1 | MOT Cover | 01/01/2022 | 0 | |||
ABC125 | NU72CAC | 1 | MOT Cover | 01/01/2022 | 0 | |||
ABC123 | NU72CAR | 1 | MOT Cover | 01/01/2022 | 1 | |||
As you can see, unique user ABC123 has renewed their product. Initial product has an inception date of 01/01/2021 and the renewal product has an inception date of 01/01/2022. The vehicle reg is the same, which is important.
Try this measure:
Renewed =
VAR _ID =
MAX ( 'Table'[Unique ID] )
VAR _Reg =
MAX ( 'Table'[Reg] )
VAR _Renewed =
IF (
COUNTROWS (
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[Unique ID] = _ID && 'Table'[Reg] = _Reg ),
'Table'[Unique ID],
'Table'[Reg],
'Table'[Inception Date]
)
) > 1,
"Renewed"
)
VAR _date =
CALCULATE (
MAX ( 'Table'[Inception Date] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Unique ID] ), NOT ISBLANK ( _Renewed ) )
)
RETURN
IF ( MAX ( 'Table'[Inception Date] ) = _date, _date )
Proud to be a Super User!
Paul on Linkedin.
@James__ ,
Can you please share sample data and sample output in table format?
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |