How to count distinct after period

[Anonymous]

I Have a table in which customer information is saved.

CustomerID	CustomerName	JoinedDate
1	Alexender	2017-08-10
2	David	2018-02-14
3	Didier	2016-01-14
4	Laurent	2019-03-02
5	Giulia	2017-01-01

My Boss has asked me to create a drill down report where in he wants all active customer count.

1st condition. suppose a customer is joined in year 2017 then for every year after 2017 the count should be as 1. 2nd condition. suppose a customer is joined in month 2018-Feb then for every month after 2018-Feb the count should be as 1.

same goes with "Day" drilldown as well.

I created a measure where in I have written below code but that didn't work. I have also created a calculated column for year and created a non active relationship between both tables.

CALCULATE( 
    DISTINCTCOUNT('Customer Status'[CustomerID]), 
    USERELATIONSHIP('Time'[Year],'Customer Status'[Customer Status Year]) 
)

This is working for year but for month it is showing same count for all month.

Requesting your help.

[Anonymous]

Here's another formula that should return the same as my previous one that uses SUMX

[# Active Customers] =
// It'll return the number
// of customers active
// according to your condition
// regardless of the time slice
// selected. Time should be
// connected on Date(!) to
// 'Customer Status'[JoinedDate]
// via a 1-to-many (one-way filtering).
var __minDate = min( Time[Date] )
var __output =
    CALCULATE(
        COUNTROWS( 'Customer Status'[CustomerID] ),
        KEEPFILTERS(
            'Customer Status'[JoinedDate] < __minDate
        )
    )
return
    // This time, if you want to return BLANKS as 0's,
    // just use __output + 0 instead of just __output.
    __output

[Anonymous]

[# Active Customers] =
// It'll return the number
// of customers active
// according to your condition
// regardless of the time slice
// selected. Time should be
// connected on Date(!) to
// 'Customer Status'[JoinedDate]
// via a 1-to-many (one-way filtering).
var __minDate = min( Time[Date] )
var __output =
    SUMX(
        DISTINCT( 'Customer Status'[CustomerId] ),
        // If the selected period in Time
        // has all dates >= JoinedDate, then
        // return 1. Otherwise 0.
        CALCULATE(
            VAR __joinedDate =
                SELECTEDVALUE( 'Customer Status'[JoinedDate] )
            return
                __minDate > __joinedDate
        )
    )
return
    // If you want to return 0's as BLANKS,
    // just use IF( __output > 0, __output )
    // instead of just returning __output.
    __output

[amitchandak]

@Anonymous , Create a date table, Create only inactive join with joining date

Current Customer = CALCULATE(COUNTx(FILTER('Customer Status','Customer Status'[Joined Date]<=max('Date'[Date]) ),('Customer Status'[CustomerID])))

Current Customer = CALCULATE(distinctcount('Customer Status'[CustomerID]), FILTER('Customer Status','Customer Status'[Joined Date]<=max('Date'[Date]) ))

if join is active
Current Customer = CALCULATE(COUNTx(FILTER('Customer Status','Customer Status'[Joined Date]<=max('Date'[Date]) ),('Customer Status'[CustomerID])),CROSSFILTER(Employee[Join Date Date],'Date'[Date],None))

refer https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[wdx223_Daniel]

@Anonymous  this might work if only the date table is filterling the visual

Distinct_Count:=VAR _MaxDate=MAX('Calendar'[Date]) RETURN CALCULATE(DISTINCTCOUNT(CustomerStatus[CustomerID]),'Calendar'[Date]<=_MaxDate)