Hi everyone, I will need your help for the following：
Basically I have a data with this format:
| Date | Customer | Join Date | Days since join | Week |
| 1/1/2021 | A | 1/1/2021 | 0 | W0 |
| 1/1/2021 | B | 1/1/2021 | 0 | W0 |
| 1/2/2021 | A | 1/1/2021 | 1 | W0 |
| 1/1/2021 | B | 1/1/2021 | 1 | W0 |
| 1/2/2021 | C | 1/2/2021 | 0 | W0 |
| 1/3/2021 | B | 1/1/2021 | 2 | W0 |
| 1/3/2021 | C | 1/2/2021 | 1 | W0 |
| 1/3/2021 | D | 1/3/2021 | 0 | W0 |
| 1/3/2021 | E | 1/3/2021 | 0 | W0 |
| … … | | | | |
| 1/15/2021 | A | 1/1/2021 | 14 | W2 |
| 1/15/2021 | B | 1/1/2021 | 14 | W2 |
| 1/15/2021 | C | 1/2/2021 | 13 | W2 |
| 1/15/2021 | E | 1/3/2021 | 12 | W2 |
| … … | | | | |
| 2/1/2021 | B | 1/1/2021 | 31 | W5 |
| 2/1/2021 | C | 1/2/2021 | 30 | W5 |
| 2/1/2021 | D | 1/3/2021 | 29 | W5 |
| 2/1/2021 | E | 1/3/2021 | 29 | W5 |
What I want to do is can have a rolling graph by date to show the followings:
1. on that day, how many active customers in total
2. on that day, how many active customers has a certain active days since their join date >= a certain number (W5)
3. on that day, how many active customers has active record in a earlier predefined active days period based on the active days since their join date (need to have a record in W2)
-- so basically each customer will have an array of Wx they have, for BCDE, their W array is {W0, W1, W2, W3, W4, W5}
-- for A it may be {W0, W1, W2, W3, W4}
-- for D it may be {W0, W1, W3, W4, W5}
4. find the intersection between #2 and #3
-- since my first condition is group of customer reach W5 on certain date (2/1/2021)
-- second condition is group of customer has record as W2 before
-- intersect these 2 group to find the common one
5. calculate the rate between #3/#2
For example, this is what I am expecting, on 2/1/2021
| On day 2/1/2021, I want to calculate the followings: | | | Notes: |
| Number of Active customers | 4 | B/C/D/E | I already got this |
| Number of customers who have >= 29 days since joined | 5 | A/B/C/D/E | Need help on this |
| Number of customers who have record on their history after 12 days since joined | 4 | A/B/C/E | Need help on this |
| Number of customers who are in both >=12 and >=29 | 3 | B/C/E | Need help on this |
I think I can find the first and second rows, but the 3rd and 4th rows, I have no idea how to do it with DAX. Can you please provide me some guidance?
I want to have seperate measure for each item above
Thanks in advance!
