Need to count no of active customers based on latest date status

[vengadesh_p]

Hi All, 

I'm new to PowerBI, Please help me achive below logic

I have two table in my datamodel (Active Customer Table & Master Calendar Table)

I have Data like below Table

Active Customer Table

I want to create a DAX query for, where we will be able to see the latest Active status of all consumers  for any date user will chose (date, week, month, quarter, year) . and also important that we see accumulative summary from all time before until the chosen date.

Expected Output 

Data

CustomerID	Date	Active status
A	1/1/2020	1
B	1/1/2020	1
C	1/1/2020	1
D	2/1/2020	1
A	3/1/2020	0
B	3/1/2020	0
B	3/2/2020	1
B	3/3/2020	0
E	3/4/2020	1
D	4/1/2020	0
A	4/1/2020	1
B	4/2/2020	1

Thanks,

Vengadesh P

[amitchandak · Accepted solution]

@vengadesh_p ,  Try a measure like

Measure =

Countx(filter(allselected(Table, Table[CustomerID]= max(Table[CustomerID]) && Table[Date]<= max(Table[Date]) && Table[Active status] =1),[CustomerID])

-

Countx(filter(allselected(Table, Table[CustomerID]= max(Table[CustomerID]) && Table[Date]<= max(Table[Date]) && Table[Active status] =0),[CustomerID])

[vengadesh_p · Accepted solution]

@amitchandak 

i have modified your Query. its working fine. Thank you... 

Active Customer = 
COUNTX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] ) &&
        'Table'[Active status] = 1
    ),
    'Table'[CustomerID]
)
-
COUNTX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] ) &&
        'Table'[Active status] = 0
    ),
    'Table'[CustomerID]
)

[amitchandak · Accepted solution]

@vengadesh_p ,  Try a measure like

Measure =

Countx(filter(allselected(Table, Table[CustomerID]= max(Table[CustomerID]) && Table[Date]<= max(Table[Date]) && Table[Active status] =1),[CustomerID])

-

Countx(filter(allselected(Table, Table[CustomerID]= max(Table[CustomerID]) && Table[Date]<= max(Table[Date]) && Table[Active status] =0),[CustomerID])

[vengadesh_p]

@amitchandak  it's show some syntex error. can you please share pbix file