Number Active between two dates

[Anonymous]

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. 

[v-luwang-msft]

Hi @Anonymous ,

Could you pls show me what output you want ,not very clear.

Best Regards

Lucien

[parry2k]

@Anonymous Good luck Do share your findings once it works. Cheers!!

[parry2k]

@Anonymous I have no idea what you mean. 

[Anonymous]

I got it to work with just a flat Excel file.  Still trying to get it to work in my data model.  

[parry2k]

@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]

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.

[parry2k]

@Anonymous to add further that screen shot is from Power BI

[parry2k]

@Anonymous ofcourse, this is a power bi forum. my question again, is that the output you are looking for?

[Anonymous]

The ultimate output would result in just the number 4.  I have a measure that calculates the total number of customers in both weeks (6).  I will be using that measure to show number active (4) / total customers (6) = 67%

[parry2k]

@Anonymous is this what you are looking for?

[Anonymous]

I should clarify that I am trying to solve this in Power BI.  I know how to do this in Excel and SQL But not PBI.