Forum Discussion
Number Active between two dates
I have data that comes in similiar to this:
| Customer ID | Customer Type | Week | Active |
| A | Type 1 | 1 | 1 |
| A | Type 1 | 2 | 1 |
| B | Type 1 | 1 | 1 |
| C | Type 2 | 2 | 1 |
| D | Type 1 | 1 | 1 |
| D | Type 1 | 2 | 1 |
| E | Type 1 | 1 | 1 |
| E | Type 1 | 2 | 1 |
| F | Type 1 | 1 | 1 |
| F | Type 1 | 2 | 1 |
The active column is calculated at the ETL level and either has a 1 or a 0. I am specifically wanting to know the number of customers who were active in Week 1 AND Week 2. Please note that the dates could change so I do not want to hardcode the dates. I have gotten close but can’t quite get it to function for me.
If I pivot the data, you can see that four customers ( A, D, E, F) were active in those two weeks.
| Sum of Active | Column Labels | ||
| Weeks | 1 | 2 | Grand Total |
| A | 1 | 1 | 2 |
| B | 1 | 1 | |
| C | 1 | 1 | |
| D | 1 | 1 | 2 |
| E | 1 | 1 | 2 |
| F | 1 | 1 | 2 |
| Grand Total | 5 | 5 | 10 |
I can't use where the sum is two because you can have decimal values come in. So I am looking for counts.. Would love help on this.
11 Replies
Anonymous is this what you are looking for?
Anonymous ofcourse, this is a power bi forum. my question again, is that the output you are looking for?
Anonymous to add further that screen shot is from Power BI
Anonymous here is the measure that will get you the count of active customers:
Total Active Customers = VAR __totalWeeks = CALCULATE ( COUNTROWS ( VALUES ( Active[Week] ) ), ALLSELECTED () ) RETURN SUMX ( VALUES ( Active[Customer ID] ), VAR __active = CALCULATE ( COUNTROWS ( Active ), Active[Active] = 1, Active[Week] ) RETURN IF ( __active >= __totalWeeks, 1 ) )✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Seems close but didn't quite get me to what I was expecting with my dataset. I was expecting a value in the 20 range and got 800,000.
This gives me 2, which is the correct number of weeks I have selected:
CALCULATE ( COUNTROWS ( VALUES ( Active[Week] ) ), ALLSELECTED () )
If I add my detail back in, the measure gives me 33 which is the number of customer ID's displaying.
Anonymous I have no idea what you mean.
Anonymous Good luck Do share your findings once it works. Cheers!!
Hi Anonymous ,
Could you pls show me what output you want ,not very clear.
Best Regards
Lucien
