Forum Discussion

Bian's avatar
Bian
Icon for Helper II rankHelper II
4 years ago
Solved

Count customers per Business Unit in a Subscription based model

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...
DAX
Model
  • Bian's avatar
    Bian
    4 years ago

    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
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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

Bian's avatar
Bian
Icon for Helper II rankHelper II
4 years ago

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