compare values of 2 tables are same or not

[Saranya_Tamil]

For every opportunity, I need to check the 'Total Existing ARR' value of opportunity table is equal to 'Total Net value' of Subscripion table or not based on below conditions.

1) start date of subscription should be before the close date of opportunity

2)End date of subscription should be after the close date of opportunity

3)If the subscription is cancelled, the cancellation date should be greater than close date of opportunity

Subscription table:

Opportunity Subscription Start              End                         Duration   Total Net Value   Revenue line                Cancellation Cancellation date

A	A	01/01/2021	31/12/2023	12	10000	SaaS	No
A	B	01/06/2022	01/06/2023	12	20000	SaaS	No
A	C	01/01/2021	31/12/2023	12	30000	Maintenance and Support	Yes	01/04/2023
A	D	01/01/2022	31/12/2023	12	15000	Maintenance and Support	No
A	F	01/01/2022	01/01/2023	12.00	15000	Maintenance and Support	No

Opportunity Table:

Customer  Opportunity  Closedate  Existing ARR SaaS  Existing ARR MS  Total Existing ARR

Customer 1	A	28/03/2023	30000	45,000.00	75,000.00
Customer 1	B	01/05/2023	30000	15,000.00	45,000.00
Customer 1	C	01/05/2023		15,000.00	15,000.00
Customer 1	D	01/05/2023		-	-

In this example, I have used the same set of subscription for all the opportunities with different close date.

Anyone can help in this DAX calculation please!

[v-jingzhang · Accepted solution]

Hi @Saranya_Tamil 

You can create the following measure to get the total of 'Total Net value' that meet the requirements. Then use another measure to compare it with the 'Total Existing ARR'. 

Net Value = 
SUMX (
    FILTER (
        'Subscription table',
        'Subscription table'[Start] < SELECTEDVALUE ( 'Opportunity Table'[Closedate] )
            && (
                (
                    'Subscription table'[End] > SELECTEDVALUE ( 'Opportunity Table'[Closedate] )
                        && 'Subscription table'[Cancellation] = "No"
                )
                    || (
                        'Subscription table'[Cancellation] = "Yes"
                            && 'Subscription table'[Cancellation date]
                                > SELECTEDVALUE ( 'Opportunity Table'[Closedate] )
                    )
            )
    ),
    'Subscription table'[Total Net Value]
)

Whether equal ? = SUM('Opportunity Table'[Total Existing ARR]) = [Net Value] 

The relationship:

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.