Forum Discussion

thartman's avatar
thartman
Regular Visitor
5 years ago

Accumulative Customer Count by Date

Hello, I have a table that has a Customer AccountID, Date Installed, and Date Cancelled.  I'm looking to create a count of how many customers were active in a given date range. My data source...
jameszhang0805's avatar
jameszhang0805
Icon for Resolver IV rankResolver IV
5 years ago

Simulated data sources:

Result :

DAX Code:

#Customer = 
CALCULATE(
    VAR _MINDate =
        MIN ( 'Date'[Date] )
    VAR _MAXDate =
        MAX ( 'Date'[Date] )
    VAR _DateRange =
        DATESBETWEEN ( 'Date'[Date], _MINDate, _MAXDate )
    VAR _Customer =
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[AccountID] ),
                _DateRange,
                'Table'[CancelDate-Recent] = BLANK ()
                    || 'Table'[CancelDate-Recent] >= _MAXDate
            )
    RETURN _Customer
    , DATESYTD( 'Date'[Date] ) 
)