Count active customers dependent on start and end date

[PeterStuhr]

Customer	Start Date	End Date
1	2020-01	2020-04
2	2020-01	2020-03
3	2020-02

Hi, I have a table like the above. I would like to calculate the number of "Active Customers" for each month. Sometimes we have an "End Date" and sometimes we don't (then they are still active).

So for the Example above, I would like an output like this:

2020-01: 2 Active (Customer 1 + 2)

2020-02: 3 Active (Customer 1 + 2 + 3)

2020-03: 3 Active (Customer 1 + 2 + 3)

2020-04: 2 Active (Customer 1 + 3)

2020-05: 1 Active (Customer 3)

2020-06: 1 Active (Customer 3)

Thanks!

[v-robertq-msft]

Hi, @PeterStuhr 

Has ryan_mayu’s solution helped you to achieve your requirement？ Because his pbix file has perfectly met your needs according to my test.

If so, would you like to mark his as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@PeterStuhr 

you need a datetime table. please see the attachment below

[PeterStuhr]

Hi @ryan_mayu 

Thanks! What if I already have a relation with my calendar date?

I currently have relation Start Date --> Calendar date.

Do I need to ignore that somehow?

[ryan_mayu]

@PeterStuhr 

If you need the relationship, you can try this

Measure =

var _MAXDATE=MAX('datetime'[Date])

RETURN CALCULATE(COUNTROWS(FILTER(ALL('Table'),'Table'[Start Date]<=_MAXDATE&&('Table'[Enddate2]>=_MAXDATE||ISBLANK('Table'[Enddate2])))))

[PeterStuhr]

Hi @ryan_mayu 

Super cool, that works!

However, it doesnt seem to be possible for me to put "legend" like Customer on it. So I can see which customer counts in which months:

Is that possible?

[ryan_mayu · Accepted solution]

@PeterStuhr 

I am afriad that will be more complicated. The current data model does not support that visual. please see the attachment below.

[PeterStuhr]

Hi, thanks!

Table 2 =

VAR _min1=MINX(FILTER('Table','Table'[Customer]=1),'Table'[Start Date])

VAR _max1=MAXX(FILTER('Table','Table'[Customer]=1),'Table'[Enddate2])

VAR _max1_1=if(ISBLANK(_max1),date(2020,12,31),_max1)

VAR tbl1=CROSSJOIN(FILTER('Table','Table'[Customer]=1),FILTER(datetime,'datetime'[Date]>=_min1&&'datetime'[Date]<=_max1_1))

VAR _min2=MINX(FILTER('Table','Table'[Customer]=2),'Table'[Start Date])

VAR _max2=MAXX(FILTER('Table','Table'[Customer]=2),'Table'[Enddate2])

VAR _max2_2=if(ISBLANK(_max2),date(2020,12,31),_max2)

VAR tbl2=CROSSJOIN(FILTER('Table','Table'[Customer]=2),FILTER(datetime,'datetime'[Date]>=_min2&&'datetime'[Date]<=_max2_2))

VAR _min3=MINX(FILTER('Table','Table'[Customer]=3),'Table'[Start Date])

VAR _max3=MAXX(FILTER('Table','Table'[Customer]=3),'Table'[Enddate2])

VAR _max3_3=if(ISBLANK(_max3),date(2020,12,31),_max3)

VAR tbl3=CROSSJOIN(FILTER('Table','Table'[Customer]=3),FILTER(datetime,'datetime'[Date]>=_min3&&'datetime'[Date]<=_max3_3))

VAR TBL=UNION(tbl1,tbl2,tbl3)

return SELECTCOLUMNS(TBL,"customer",'Table'[Customer],"date",'datetime'[Date])

I can see that you do something specifically for Customer=1,2,3 etc.

What if we get new customers all the time, do I have to make new codes for each new customer ID?

[ryan_mayu]

@PeterStuhr 

your data model does not support you to show that visual and that is also not a good choice. What if you have 100 customers in a month? one chart will be separated to 100 parts.

I suggest you only show numbers and do not put customer ID to legend.