Forum Discussion
count Distinct customers per month
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
Great, if it works could you mark it as a solution for others to find please 🙂
3 Replies
- SamWiseOwl
Super User
Hi Anonymous
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.
- SamWiseOwl
Great, if it works could you mark it as a solution for others to find please 🙂