topic Re: Total Customers (end of month) in DAX Commands and Tips

Total Customers (end of month)

wyanjaspew — Sun, 24 Aug 2025 23:59:58 GMT

Hi Everyone,

Could you help me configure this? create a DAX measure that calculate the Total Customers (end of month) based on total customers at eriod of time and cancelled customers.

Note: all metrics was calculated by measures.

pbidaxhelp daxdax dax daxdaxdax

Re: Total Customers (end of month)

MarcoSparkBI — Mon, 25 Aug 2025 02:18:23 GMT

hi,
wyanjaspew , as you are not provide your dataset and can not see your relationship between tables. however, i can still provide you as a reference solution. assume you have a customer table like below.

you have a calendar table which has a ralationship between startdat(active),end data(inactive).
here is the dax formula:

TotalCustomerRecords = COUNTROWS(CustomerTbl),

Total Customertbl Exist end of month =

VAR _EndOfMonth = MAX ( CustomerTbl[StartDate] )

RETURN

CALCULATE (

COUNTROWS ( Customertbl ),

FILTER (

ALL ( Customertbl ),

Customertbl[StartDate] <= _EndOfMonth &&

( ISBLANK ( Customertbl[EndDate] ) || Customertbl[EndDate] > _EndOfMonth )

)

NewCustomers =

VAR MindateInMonth=STARTOFMONTH('Calendar'[Date])

VAR MaxDateInMonth=ENDOFMONTH('Calendar'[Date])

return

CALCULATE(DISTINCTCOUNT(CustomerTbl[CustomerID]),

CustomerTbl[StartDate]>=MindateInMonth&&CustomerTbl[StartDate]<=MaxDateInMonth)

Cancelling Customers =

CALCULATE (

COUNTROWS ( CustomerTbl ),

USERELATIONSHIP ( CustomerTbl[EndDate], Calendar[Date] )

here is the result:

the dataset difference, relationship difference, you may encounter a different results. but i believe you can still using the basics like ENDOFMONTH/STARTOFMONTH. To get what you want
Regars.

Re: Total Customers (end of month)

wyanjaspew — Mon, 25 Aug 2025 02:43:03 GMT

Hi MarcoSparkBI,

This is the outcome I'm aiming for (highlighted in yellow) based on the sample data you provided.

Re: Total Customers (end of month)

MarcoSparkBI — Mon, 25 Aug 2025 03:11:36 GMT

HI, wyanjaspew ,
Here is the udpate DAX .

Total Customertbl Exist end of month =

VAR _EndOfMonth = ENDOFMONTH( CustomerTbl[StartDate] )

VAR _EndOfCAN = CALCULATE(MAX(CustomerTbl[EndDate]),USERELATIONSHIP ( CustomerTbl[EndDate], Calendar[Date] ))

RETURN

CALCULATE (

COUNTROWS ( Customertbl ),

FILTER (

ALL ( Customertbl ),

Customertbl[StartDate] <= _EndOfMonth &&

( ISBLANK ( Customertbl[EndDate] ) || Customertbl[EndDate] > _EndOfCAN )

)

and results should be what you want.

regards.

Re: Total Customers (end of month)

wyanjaspew — Mon, 25 Aug 2025 04:32:05 GMT

Hi MarcoSparkBI

It looks like the code isn't displaying as expected. (red highlighted)

Here’s the DAX formula I applied.

Measure =

VAR TotalCustomerRecords = DISTINCTCOUNT(crm_services[serviceid])

VAR _EndOfMonth = ENDOFMONTH(LKP_calendar[Date])

VAR _EndOfCAN = CALCULATE(MAX(LKP_calendar[Date]), USERELATIONSHIP(crm_churn_services[disconnectiondate], LKP_calendar[Date]))

RETURN
CALCULATE (
TotalCustomerRecords,
FILTER (
ALL ( crm_services ),
crm_services[activationdate] <= _EndOfMonth &&
( ISBLANK (crm_services[disconnectiondate] ) || crm_services[disconnectiondate] > _EndOfCAN )
)
)

Re: Total Customers (end of month)

FBergamaschi — Mon, 25 Aug 2025 11:24:20 GMT

Can you show your data (a sample of every table)? So we can fix the formula? Thanks

Re: Total Customers (end of month)

wyanjaspew — Mon, 25 Aug 2025 12:04:23 GMT

FBergamaschi,

Here's the sample datasets for each tables.

LKP_calendar

crm_services (All active and disconnected services)

crm_churn_services (All disconnected services)

Re: Total Customers (end of month)

FBergamaschi — Mon, 25 Aug 2025 14:21:49 GMT

wyanjaspew I forgot to ask dat ain a usable format (not pictures)

Thanks

Re: Total Customers (end of month)

wyanjaspew — Mon, 25 Aug 2025 23:30:27 GMT

Hi FBergamaschi,

LKP_calendar

Date	Year	Month	MonthName	YearMonth	Quarter
1/1/2024	2024	1	January	2024-January	Q1
2/1/2024	2024	1	January	2024-January	Q1
3/1/2024	2024	1	January	2024-January	Q1
4/1/2024	2024	1	January	2024-January	Q1
5/1/2024	2024	1	January	2024-January	Q1
6/1/2024	2024	1	January	2024-January	Q1
7/1/2024	2024	1	January	2024-January	Q1
8/1/2024	2024	1	January	2024-January	Q1

crm_services

productcategoryname	productitemname	customertypename	groupname	activationdate	serviceid	disconnectiondate	accountnumber
Mobile	5G	Residential	Telco	8/8/2025	423928885		179303
Mobile	5G	Residential	Telco	8/8/2025	452004702		202961
Mobile	5G	Residential	Telco	8/8/2025	411057559		207697
Mobile	5G	Residential	Telco	8/8/2025	431523333		208578
Mobile	5G	Residential	Telco	8/8/2025	412261340		216086
Mobile	5G	Residential	Telco	8/8/2025	497722111		217916
Mobile	5G	Residential	Telco	8/8/2025	492979868		220547
Mobile	5G	Residential	Telco	8/8/2025	430088991		230109
Mobile	4G	Residential	Telco	8/8/2025	493101359		260266
Mobile	4G	Residential	Telco	8/8/2025	433836182		263404
Mobile	4G	Residential	Telco	8/8/2025	452344968		264560
Mobile	4G	Residential	Telco	8/8/2025	420222407		265482
Mobile	4G	Residential	Telco	8/8/2025	404874188		272960
Mobile	4G	Residential	Telco	8/8/2025	430792655		272960
Mobile	4G	Residential	Telco	8/8/2025	418944864		333116
Mobile	4G	Residential	Telco	8/8/2025	420277301		346017
Mobile	4G	Residential	Telco	8/8/2025	470265867		366208
Mobile	4G	Residential	Telco	8/8/2025	492974711		366208
Mobile	4G	Residential	Telco	8/8/2025	404667965		368753
Mobile	4G	Residential	Telco	8/8/2025	432708084		391364
Mobile	4G	Residential	Telco	8/8/2025	491106335		398313

crm_churn_services

productcategoryname	serviceid	disconnectiondate	groupname	productitemname	customertypename	accountnumber
Mobile	4003368495	27/11/2023	Telco	4G	Residential	158837
Mobile	r1y9h482976	27/11/2023	Telco	4G	Residential	176495
Mobile	9911798076	27/11/2023	Telco	4G	Residential	179660
Mobile	6706718440	27/11/2023	Telco	4G	Residential	18682
Mobile	18611110227	27/11/2023	Telco	4G	Residential	231695
Mobile	j6p9d228669	27/11/2023	Telco	4G	Residential	232858
Mobile	18706112930	27/11/2023	Telco	4G	Residential	239486
Mobile	c4s3r241259	27/11/2023	Telco	4G	Residential	240766
Mobile	c3j1j254100	27/11/2023	Telco	4G	Residential	246372
Mobile	v7m8t249661	27/11/2023	Telco	5G	Residential	246379
Mobile	s9p3w255045	27/11/2023	Telco	5G	Residential	250048
Mobile	1.00075E+13	27/11/2023	Telco	5G	Residential	251531
Mobile	z8g0b258487	27/11/2023	Telco	5G	Residential	252208
Mobile	k5h2r268760	27/11/2023	Telco	5G	Residential	259982
Mobile	p2g3f270488	27/11/2023	Telco	5G	Residential	260384
Mobile	3006974668	27/11/2023	Telco	5G	Residential	260781
Mobile	10143395789	27/11/2023	Telco	5G	Residential	268725
Mobile	c4p5k281808	27/11/2023	Telco	5G	Residential	270974
Mobile	g6c6f282309	27/11/2023	Telco	5G	Residential	271371

Re: Total Customers (end of month)

MarcoSparkBI — Tue, 26 Aug 2025 00:59:46 GMT

wyanjaspew ，hi , please make sure that date tables has two relationships, one is date to activationdate, one to many relationship(active), the other one is date to dis connectiondate, one to many relashionship,(inactive).
instead of using variables to calculate TotalCustomerRecords , you can try to modify to below
DAX:

Measure =

VAR TotalCustomerRecords = DISTINCTCOUNT(crm_services[serviceid])

VAR _EndOfMonth = ENDOFMONTH(LKP_calendar[Date])

VAR _EndOfCAN = CALCULATE(MAX(LKP_calendar[Date]), USERELATIONSHIP(crm_churn_services[disconnectiondate], LKP_calendar[Date]))

RETURN
CALCULATE (
DISTINCTCOUNT(crm_services[serviceid]),
FILTER (
ALL ( crm_services ),
crm_services[activationdate] <= _EndOfMonth &&
( ISBLANK (crm_services[disconnectiondate] ) || crm_services[disconnectiondate] > _EndOfCAN )
)
)

please have a try. or provide a simple PBIx data, i can help to take a look in deep
regards.

Re: Total Customers (end of month)

v-sgandrathi — Tue, 26 Aug 2025 05:57:23 GMT

Hi wyanjaspew,

Glad you shared the details. You can calculate the Total Customers (End of Month) as a cumulative running total based on new activations and disconnections. Please try the below approach:

Create measure for New Customers:
New Customers =
CALCULATE (
    DISTINCTCOUNT ( crm_services[serviceid] ),
    KEEPFILTERS ( VALUES ( LKP_calendar[Date] ) )
)

Create measure for Cancelled Customers:
Cancelled Customers =
CALCULATE (
    DISTINCTCOUNT ( crm_churn_services[serviceid] ),
    KEEPFILTERS ( VALUES ( LKP_calendar[Date] ) )
)

Finally, create measure for Total Customers (End of Month) as a cumulative net:
Total Customers (End of Month) =
VAR MaxDate = MAX ( LKP_calendar[Date] )
RETURN
    [Initial Customers] +
    CALCULATE (
        SUMX (
            VALUES ( LKP_calendar[Date] ),
            [New Customers] - [Cancelled Customers]
        ),
        FILTER ( ALL ( LKP_calendar ), LKP_calendar[Date] <= MaxDate )
    )

I have attached the file as well, you can check the output
Thank you.

Re: Total Customers (end of month)

v-sgandrathi — Fri, 29 Aug 2025 05:39:17 GMT

Hi wyanjaspew,

Just a quick check-in! Has your issue been resolved with the information we shared? We’d be delighted to help further if needed.

Thank you.

Re: Total Customers (end of month)

v-sgandrathi — Mon, 01 Sep 2025 11:01:37 GMT

Hi wyanjaspew,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Re: Total Customers (end of month)

v-sgandrathi — Thu, 04 Sep 2025 08:24:30 GMT

Hi wyanjaspew,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!