Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
vengadesh_p
Helper I
Helper I

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

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

test1.png

 

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 
test.png

 

Data

CustomerIDDateActive status
A1/1/20201
B1/1/20201
C1/1/20201
D2/1/20201
A3/1/20200
B3/1/20200
B3/2/20201
B3/3/20200
E3/4/20201
D4/1/20200
A4/1/20201
B4/2/20201

 

 


Thanks,

Vengadesh P

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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])

View solution in original post

vengadesh_p
Helper I
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]
)

 

View solution in original post

3 REPLIES 3
vengadesh_p
Helper I
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]
)

 

amitchandak
Super User
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])

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

Screenshot_2.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.