Cumulative amount of users on specific date

[Anonymous]

Hello fellow PowerBI enthusiasts,

I cannot get my head around the following problem.

I have one table called CUSTOMER showing all of my customer ID’s:

Customer ID	Sign up date
1	1-1-2018
2	1-2-2018
3	1-3-2018
4	1-4-2018
5	1-4-2018
6	1-4-2018
7	1-5-2018
8	1-6-2018

I have another table called PROMOTIONS showing all of my promotions:

Promotion name	Start date of promotion
A	1-2-2018
B	1-3-2018
C	1-4-2018
D	1-5-2018

What I want is a column in my promotions table, that tells me the total amount of customers that were signed up at the day the promotions started. How do I get about that?

Promotion name	Start date of promotion	Example column: Amount of customers at the start of promotion
A	1-2-2018	2
B	1-3-2018	3
C	1-4-2018	6
D	1-5-2018	7

FYI In this example the customer ID’s are numbers and every new customer has a follow up number. In the actual data this is not the case; the customer ID is a random GUID.

Thanks in advance!

[PattemManohar · Accepted solution]

@Anonymous Please try this as a "New Column" in your promotions table.

Result = CALCULATE(COUNTROWS(Test149Customers),FILTER(Test149Customers,Test149Customers[SignUpDate]<=Test149Promotions[StartDateofPromotion])) 

[Anonymous]

That's so weird, I've tried a similar formula but it just wouldn't work, but this one works indeed. Thanks a lot for your help @PattemManohar!