Count customers per Business Unit in a Subscription based model

[Bian]

Trying to solve a measure for a subscription based business.

The measure should calculate number of customers per month.

I work with a dataset that stores all the changes on the subscription. I can therefore have man rows per subscription.

The measure works fine except when a subscription is moved between Business units.

The customer then counts on both buinsess units instead of just the correct one.

The measure also counts correct when looking at the whole business.

I tried adding All(Business units) in both MaxPricedates and Results without effect.

Measure:

Number customers = 
VAR MaxDate =  
    MAX ( Date[Date] )
VAR FromDate = 
    ENDOFMONTH(Date[Date])
VAR EndDate = 
ENDOFMONTH('Subscriptions'[ActualDate])
VAR EndOfLifeDate =
    STARTOFMONTH(Date[Date])

VAR MaxPrisDates =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ('Subscriptions','Subscriptions'[SubscriptionID]), 
            "@MaxBalanceDate", CALCULATE (Max('Subscriptions'[ActualDate]))
        ),
        Date[Date] <= MaxDate
        

    )
VAR MaxPrisDatesWithLineage =
    TREATAS ( MaxPrisDates, 'Subscriptions'[SubscriptionID], Date[Date]) 
VAR Result =
    CALCULATE( 
        DISTINCTCOUNT( 'Subscriptions'[AccountID]),
        MaxPrisDatesWithLineage,
        'Subscriptions'[Start date] <= FromDate, 
        'Subscriptions'[End date] >= EndOfLifeDate, 
        'Subscriptions'[Status] <> "Other",
        'Subscriptions'[Status] <> "Stopped"
        
        )
RETURN
   Result

Grateful for assistans

[amitchandak]

@Bian ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Bian · Accepted solution]

Solutions was actually what i already tried, that is to use All(Business Units). This time in the right place.

Number customers = 
VAR MaxDate =  
    MAX ( Date[Date] )
VAR FromDate = 
    ENDOFMONTH(Date[Date])
VAR EndDate = 
ENDOFMONTH('Subscriptions'[ActualDate])
VAR EndOfLifeDate =
    STARTOFMONTH(Date[Date])

VAR MaxPrisDates =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ('Subscriptions','Subscriptions'[SubscriptionID]), 
            "@MaxBalanceDate", CALCULATE (Max('Subscriptions'[ActualDate]))
        ),
        All('Business Units'),
        Date[Date] <= MaxDate
        

    )
VAR MaxPrisDatesWithLineage =
    TREATAS ( MaxPrisDates, 'Subscriptions'[SubscriptionID], Date[Date]) 
VAR Result =
    CALCULATE( 
        DISTINCTCOUNT( 'Subscriptions'[AccountID]),
        MaxPrisDatesWithLineage,
        'Subscriptions'[Start date] <= FromDate, 
        'Subscriptions'[End date] >= EndOfLifeDate, 
        'Subscriptions'[Status] <> "Other",
        'Subscriptions'[Status] <> "Stopped"
        
        )
RETURN
   Result