count Distinct customers per month

[hpatel24779]

Hi,

I have a table with customer id, service type, service start and service end. i also have a calendar table.

i want to show per month in a graph, how many unique active users there were. i have searched online and gone through some of the examples but none come close to what i want to achieve.

the logic is to count anyone who's service start is before the start of current month and end date is blank or on or after the start of current month.

can anyone help!!

kind regards

Hetal

[SamWiseOwl]

Hi @hpatel24779 

Unique people =

var currentdate = firstdate(calendar[date])

var finaldate = lastdate(calendar[date])

Return
Calculate( distinctcount(table[customer id]), table[startdate] <= Currentdate && ( table[enddate] >=finaldate || isblank(table[enddate]))

Capture the start and end date of the current month filter.

Filter the table to everyone whose start is before the earliest date and after the current date or blank.

[hpatel24779]

@SamWiseOwl thank you for this. it works perfectly. much appreciated

[SamWiseOwl · Accepted solution]

Great, if it works could you mark it as a solution for others to find please 🙂