Search code examples
powerbidaxvisualizationmeasure

Can I make a DAX measure to count households lost based on non-contract renewal and service category?

I'm back again. This time I am having trouble as per the question.

Before I layout the problem, here are the layout of the tables I am working with:

  • Household Table:
HouseholdID Name OwnerID OwnerName StateCodeName Service Category
LOIF7-JGN25-453JG Smith, Sam C20R2-991MN-LOP92 Chris R Active Platinum
HG78G-KJS00-HB524 Capaldi, Lewis C20R2-991MN-LOP92 Chris R Active D
JJK83-IOP0S-MG674 Spears, Britney C20R2-991MN-LOP93 Chris R Active B
GJ5V6-BBNS8-992NS Black, William C20R2-991MN-LOP94 Chris R Inactive C
NNBS2-GJS6T-578TF Swift, Taylor C20R2-991MN-LOP95 Chris R Inactive A
  • Service Period Table:
serviceperiodID OwnerID OwnerName statecodename Startdate Enddate Serviceperiodname HouseholdID totalfee totalfee x gst Current Stage Name
AD123-15GAT-OPSSS C20R2-991MN-LOP92 Chris R Inactive 25/01/2023 24/01/2024 Smith, Sam - 25/1/2023 to 24/1/2024 LOIF7-JGN25-453JG $5,500 $5,000.00 Service Period Inactive
HSB94-875JG-6G8GD C20R2-991MN-LOP92 Chris R Active 25/01/2024 25/01/2025 Smith, Sam - 25/1/2024 to 24/1/2025 LOIF7-JGN25-453JG $11,000 $10,000.00 Service Period Active
HG049-Y78JF-LAK25 C20R2-991MN-LOP93 Chris R Active 14/06/2023 13/06/2024 Spears, Britney - 14/6/2023 to 13/6/2024 JJK83-IOP0S-MG674 $3,300 $3,000.00 Service Period Active
NAUS5-JS94B-VT6R7 C20R2-991MN-LOP94 Chris R Inactive 20/04/2022 19/04/2023 Spears, Britney - 20/4/2022 to 19/4/2023 JJK83-IOP0S-MG674 $2,200 $2,000.00 Service Period Inactive
BHY24-HGF24-JGN51 C20R2-991MN-LOP95 Chris R Inactive 5/01/2023 4/01/2024 Swift, Taylor - 5/1/2023 to 4/1/2024 NNBS2-GJS6T-578TF $5,500 $5,000.00 Service Period Inactive
NS836-JG98T-287JF C20R2-991MN-LOP96 Chris R Inactive 6/06/2022 5/06/2023 Black, William - 6/6/2022 to 5/6/2023 GJ5V6-BBNS8-992NS $2,000 $1,818.18 Service Period Inactive
LLSK0-002G8-JJJGS C20R2-991MN-LOP96 Chris R Inactive 3/01/2023 2/01/2024 Capaldi, Lewis - 3/1/2023 to 2/1/2024 HG78G-KJS00-HB524 $880 $800.00 Service Period Inactive

The tables are in a one-to-many relationship with the HouseholdID columns.

I am trying to calculate how many households I have lost from last year. When I say a lost household, I mean that the household no longer has any active contracts (or service period) with us as they did not renew from last year's contract. There are 2 ways for these clients to exist in the database. Their household is considered "Inactive" under the 'Household'[statecodename] column, or they are tagged as a "D" in the 'Household'[ServiceCategory] column. Being tagged as Service Category 'D' means we are performing other services for them outside of the contracts.

So using the example tables above, and going from todays date the 7th of Feb 24, the counter should equal 2 as we have lost Taylor Swift (Household inactive and did not renew this year) and Lewis Capaldi (Household tagged "D" and did not renew this year). If we were to reach later in the year and Britney Spears did not renew the contract, then the counter would go to 3 after we get past the end date of that contract/service period as the household would be tagged as inactive if no other services were occurring.

I have tried a few different measures, but keep getting a large number back, when I know realistically the number should be 0 as all clients have renewed their contract so far this year.

Households Lost_ = 
VAR CurrentYear = YEAR(TODAY())
VAR EndOfYear_ = DATE(CurrentYear, 12, 31)
RETURN
    CALCULATE(
        COUNTROWS(SUMMARIZE('Merged - Service Period', 'Merged - Service Period'[finpal_householdid])),
        FILTER(
            'Merged - Service Period',
            'Merged - Service Period'[finpal_enddate] <= EndOfYear_ &&
            'Merged - Service Period'[finpal_enddate] > date(2022, 12, 31) &&
            'Merged - Service Period'[finpal_enddate] < TODAY()
        )
    )

If anyone could give me some DAX code to help me out with this, that would be much appreciated!

Solution

  • Try something along the lines of:

    Households Lost = 
  var households = 
    SUMMARIZE(
      'Merged - Service Period',
      [finpal_householdid],
      "endDate",
        CALCULATE(
          MAX([finpal_enddate]),
          ALLEXCEPT('Merged - Service Period', 'Merged - Service Period'[finpal_householdid])
        )
    )
  return
    COUNTROWS(
      FILTER(
        households,
        YEAR([endDate]) = YEAR(TODAY()) && [endDate] <= TODAY()
      )
    )