CODEWITHSUNDEEP
powerbipowerbi-desktopintersect
Shalabh A
Shalabh A

Reputation: 1

Power Bi - Intersect function to return qty

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.

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

Upvotes: 0

Views: 34

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

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
    )

Upvotes: 0

Related Questions