cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

Hi All,

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,

2 ACCEPTED SOLUTIONS
Super User

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

Helper I

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

3 REPLIES 3
Helper I

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

Super User

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

Helper I

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors