Count Return Policy in life insurance

[ttdtbv]

Dear all,

I have a problem, please help me.

I want to count Return Policy?

in which "Return Policy is:

               1/ Policy have StartDate within the EndDate +- 3 month {datediff(EndDate, StartDate, month)<=3}

               2/ Repeat Buyer or Repeat Receiver or Buyer become Receiver or Receiver become Buyer"

Many thanks 

Data: 

https://docs.google.com/spreadsheets/d/15H3JKehCXx9feFgR-c-0w31t5NsZx4ko/edit?usp=sharing&ouid=10231...

PolicyId	StartDate	EndDate	Buyer	Receiver
1	01/01/2000	01/01/2022	B1	R1
2	01/02/2022	01/02/2030	B1	R2	Return Policy, B1 repeat, datediff(EndDate, StartDate, month)<=3
3	01/05/2022	01/05/2030	B1	R3	Not Return Policy, datediff(EndDate, StartDate, month) > 3
4	01/03/2022	01/03/2030	R1	R4	Return Policy, R1 repeat from Receiver to Buyer, datediff(EndDate, StartDate, month)<=3
5	01/02/2022	01/02/2030	B2	R5	Not Return Policy, not repeat Buyer or Receiver

[v-yiruan-msft · Accepted solution]

Hi @ttdtbv ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create two measures as below to get it:

Flag = 
VAR _selbuyer =
    SELECTEDVALUE ( 'Table'[Buyer] )
VAR _selreceiver =
    SELECTEDVALUE ( 'Table'[Receiver] )
VAR _selsdate =
    SELECTEDVALUE ( 'Table'[StartDate] )
VAR _seledate =
    SELECTEDVALUE ( 'Table'[EndDate] )
VAR _preedate =
    CALCULATE (
        MAX ( 'Table'[EndDate] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            ( 'Table'[Buyer] = _selbuyer
                || 'Table'[Receiver] = _selbuyer )
                && 'Table'[EndDate] < _seledate
        )
    )
VAR _datediff =
    DATEDIFF ( _selsdate, _preedate, MONTH )
RETURN
    IF ( ISBLANK ( _datediff ), BLANK (), IF ( _datediff <= 3, 1, BLANK () ) )

Count of return policy = COUNTX ( FILTER ( ALLSELECTED ( 'Table'[PolicyId] ), [Flag] = 1 ), [PolicyId] )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards