Count

[manideep547]

Hi All,
Here is my measure for Active and Inactive customers 

Customer_Status_min_Active =

VAR MinDate =

MIN ( 'Date'[Date])

VAR InPast6MonthsDate = DATE ( YEAR ( MinDate ), MONTH ( MinDate ) - 6, DAY ( MinDate ) )

RETURN

SWITCH (

TRUE,

CALCULATE (

COUNTROWS ( 'Union Table'),

FILTER (

'Union Table',

'Union Table'[TranscationID] > InPast6MonthsDate

&& 'Union Table'[TranscationID] < MinDate

)

) <> 0, "Active",

CALCULATE (

COUNTROWS ('Union Table'),

FILTER ( 'Union Table', 'Union Table'[TranscationID] < InPast6MonthsDate )

) <> 0,

"Inactive"

)
count of the active  is 

Active-count-Min = COUNTROWS(FILTER(SUMMARIZE('Union Table','Union Table' [CustomerID],"Status",[Customer_Status_min_Active]),[Status]="Active"))

count of the Inactive  is 

Active-count-Min = COUNTROWS(FILTER(SUMMARIZE('Union Table','Union Table' [CustomerID],"Status",[Customer_Status_min_Active]),[Status]="Inactive"))


Here my problem is  
If we have 10 ACTIVE customers - Those who have been active in the past 6 months from the selected period 
-If 5 out of those 10 became INACTIVE - Those who became inactive in +6 months  

count =5

[v-lionel-msft]

Hi @manideep547 ,

Did you try to join ALLSELECTED() function into your formula?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[manideep547]

You can find the .pdix file below link 
https://drive.google.com/open?id=1FbjcQZUZIyWKmq-ru7ToTmPTc7aN6YCZ

if I selected dates in slicer 1/1/2018 to 1/1/2019

active Customer Means customer who has transactions within 6 months that means transactions between in 1/7/2017 to 1/1/2018
 between those dates as know as active customers 
Inactive Customer Means transactions  less than 1/7/2017 are known as Inactive 
count=if customer 1 has a transaction in 1/6/2017 and also has a transaction in 1/8/2019 that means customer 1 active now and inactive in past 
I need below value and %
-If we have 10 ACTIVE customers - Those who have been active in the past 6 months from the selected period who have an Average AED500 spend per customer
-If 5 out of those 10 became INACTIVE - Those who became inactive in +6 months who have an Overall Average Spend of AED500 per customer
-DRR (Value) = (5*500) = AED2,500 DCR à (2,500) - 2,500 = AED2,500 DRR
-DRR (%) = ((5*500) / (10* 500) *100% = 50% DCR à 50% - 100% = 50% DRR
@amitchandak