Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 ...
Need Help
parry2k's avatar
parry2k
Super User
5 years ago

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.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.