Power Bi - Intersect function to return qty

Question

Consider the raw data below

opptyID	Account ID	Product	Close	Start	End	Qty
12	1	A	01-Jan-23	01-Jan-23	01-Feb-23	10.00
13	1	A	01-Feb-23	01-Feb-23	10-Sep-23	2.00
17	2	A	01-Feb-23	01-Feb-23	10-Sep-23	100.00
14	1	A	01-Jan-23	01-Feb-23	01-May-23	4.00
15	1	A	01-Dec-22	01-Dec-22	01-Mar-23	40.00
16	1	A	01-Mar-23	01-Mar-23	01-Aug-23	39.00

I want to find what is the renewal qty.

• Renewal oppty – when a lic is expiring. For example, for Q2 (Feb-Apr) renewal oppty is 50 Lic
• Renewal – against this oppty, how many lics are starting for each account. For example, for Q2, 45 are renewing (opptyID 17 is not counted as its account ID is 2 and account ID 1 is expiring in Q2)

After exploring several ways, I am thinking of implementing this logic

1. Expiry table : Create a table summarized by account ID of lic expiring in given quarter
2. Start table: Create a table summarized by account ID of lic starting in given quarter
3. Find intersection between the two
   • If ID is present, it means it was expiring and renewing - > renewal qty is from the start table
   • If ID present in only expiry table, means there was no renewal
   • If ID present in only start table, means there was no renewal opportunity – we sold more that’s it.

To implement the above, I tried the below 2 ways

Way 1: assign expiry and start table to variables and each table contains the account and qty (summarized by account)
Problem – I am unable to use intersect function while referencing the IDs from the variables. (I need to use only ID column as intersect will otherwise also match qty column)

Way 2: create the expiry and start tables with only account id so intersect is easy to code.
Problem – how do I return the qty from start table for the IDs common?

Here is the code for

Renewal via Tables = 
//to find the IDs with lic expiring in period
VAR lic_expire =
    CALCULATETABLE (
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[Start] <= MAX ( fiscaldate[Date] )
                && Sheet1[End] >= MIN ( fiscaldate[Date] )
                && Sheet1[End] <= MAX ( fiscaldate[Date] )
        ),
        Sheet1[Account ID]
    )
//to find the IDs with lic starting in period   
VAR lic_start =
    CALCULATETABLE (
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[End] > MAX ( fiscaldate[Date] )
                && Sheet1[Start] >= MIN ( fiscaldate[Date] )
                && Sheet1[Start] <= MAX ( fiscaldate[Date] )
        ),
        Sheet1[Account ID]
    )

VAR commonIDs = INTERSECT(lic_expire, lic_start)

RETURN 
CALCULATE(
    SUMX (
        SUMMARIZE (
            CALCULATETABLE (
                FILTER (
                    ALL ( Sheet1 ),
                    Sheet1[End] > MAX ( fiscaldate[Date] )
                        && Sheet1[Start] >= MIN ( fiscaldate[Date] )
                        && Sheet1[Start] <= MAX ( fiscaldate[Date] )
                        && Sheet1[Account ID] IN {INTERSECT(lic_expire,lic_start)} *//This fails. I need to filter only common IDs to get the total start qty*
                ),
                Sheet1[Account ID]
            ),
            "start qty", SUM ( Sheet1[Qty] )
        ),
        [start qty]
    )
)

Here is the link to the pib https://www.dropbox.com/scl/fi/07hw6ofvtsd53jtclhnaf/playarea.pbix?rlkey=nil13kag9crh7zwxlw29fdap5&dl=0

Answer 1

Try the following:

Qty Renewal = 
  var minD = MIN(fiscaldate[Date])
  var maxD = MAX(fiscaldate[Date])
  var curIds = DISTINCT(Sheet1[Account ID])
  var accountIdsExpire =
    CALCULATETABLE(
      DISTINCT(Sheet1[Account ID]),
      ALL(Sheet1),
      Sheet1[Start] <= maxD
      && Sheet1[End] >= minD
      && Sheet1[End] <= maxD
    )

  RETURN 
    CALCULATE(
      SUM(Sheet1[Qty]),
      ALL(Sheet1),
      Sheet1[Account ID] IN INTERSECT(curIds, accountIdsExpire),
      Sheet1[End] > maxD
      && Sheet1[Start] >= minD
      && Sheet1[Start] <= maxD
    )