Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
wyanjaspew
Advocate I
Advocate I

Total Customers (end of month)

‎08-24-2025 04:45 PM

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.

wyanjaspew_0-1756078861040.png


@pbidaxhelp @daxdax @dax @daxdaxdax 

Message 1 of 14
915 Views
0
13 REPLIES 13
v-sgandrathi
Community Support
Community Support

‎08-25-2025 10:50 PM

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

vsgandrathi_0-1756187243573.png

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

4806034 1.pbix
Message 11 of 14
555 Views
0
v-sgandrathi
Community Support
Community Support
In response to v-sgandrathi

‎08-28-2025 10:39 PM

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.

Message 12 of 14
437 Views
0
v-sgandrathi
Community Support
Community Support
In response to v-sgandrathi

‎09-01-2025 04:01 AM

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.

Message 13 of 14
264 Views
0
v-sgandrathi
Community Support
Community Support
In response to v-sgandrathi

Thursday

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!

Message 14 of 14
142 Views
0
MarcoSparkBI
Frequent Visitor

‎08-24-2025 07:18 PM

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.

MarcoSparkBI_0-1756088093698.png

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:
MarcoSparkBI_1-1756088215260.png

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.

Message 2 of 14
858 Views
wyanjaspew
Advocate I
Advocate I
In response to MarcoSparkBI

‎08-24-2025 07:41 PM

Hi @MarcoSparkBI,

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

wyanjaspew_0-1756089569747.png

 

Message 3 of 14
846 Views
0
MarcoSparkBI
Frequent Visitor
In response to wyanjaspew

‎08-24-2025 08:11 PM

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.
MarcoSparkBI_0-1756091480341.png

regards.

Message 4 of 14
826 Views
wyanjaspew
Advocate I
Advocate I
In response to MarcoSparkBI

‎08-24-2025 08:35 PM

Hi @MarcoSparkBI 

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

wyanjaspew_0-1756092725297.png


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

 




Message 5 of 14
818 Views
0
MarcoSparkBI
Frequent Visitor
In response to wyanjaspew

‎08-25-2025 05:59 PM

@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.

Message 10 of 14
614 Views
0
FBergamaschi
Solution Sage
Solution Sage
In response to wyanjaspew

‎08-25-2025 04:24 AM

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

Message 6 of 14
748 Views
0
wyanjaspew
Advocate I
Advocate I
In response to FBergamaschi

‎08-25-2025 05:03 AM

@FBergamaschi,

Here's the sample datasets for each tables.

LKP_calendar

wyanjaspew_1-1756123243940.png

 

crm_services (All active and disconnected services)

wyanjaspew_2-1756123280867.png


crm_churn_services (All disconnected services)

wyanjaspew_3-1756123375406.png

 

Message 7 of 14
736 Views
0
FBergamaschi
Solution Sage
Solution Sage
In response to wyanjaspew

‎08-25-2025 07:21 AM

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

 

Thanks

Message 8 of 14
702 Views
0
wyanjaspew
Advocate I
Advocate I
In response to FBergamaschi

‎08-25-2025 04:30 PM

Hi @FBergamaschi,

LKP_calendar

DateYearMonthMonthNameYearMonthQuarter
1/1/202420241January2024-JanuaryQ1
2/1/202420241January2024-JanuaryQ1
3/1/202420241January2024-JanuaryQ1
4/1/202420241January2024-JanuaryQ1
5/1/202420241January2024-JanuaryQ1
6/1/202420241January2024-JanuaryQ1
7/1/202420241January2024-JanuaryQ1
8/1/202420241January2024-JanuaryQ1



crm_services

productcategorynameproductitemnamecustomertypenamegroupnameactivationdateserviceiddisconnectiondateaccountnumber
Mobile5G ResidentialTelco8/8/2025423928885 179303
Mobile5G ResidentialTelco8/8/2025452004702 202961
Mobile5G ResidentialTelco8/8/2025411057559 207697
Mobile5G ResidentialTelco8/8/2025431523333 208578
Mobile5G ResidentialTelco8/8/2025412261340 216086
Mobile5G ResidentialTelco8/8/2025497722111 217916
Mobile5G ResidentialTelco8/8/2025492979868 220547
Mobile5G ResidentialTelco8/8/2025430088991 230109
Mobile4GResidentialTelco8/8/2025493101359 260266
Mobile4GResidentialTelco8/8/2025433836182 263404
Mobile4GResidentialTelco8/8/2025452344968 264560
Mobile4GResidentialTelco8/8/2025420222407 265482
Mobile4GResidentialTelco8/8/2025404874188 272960
Mobile4GResidentialTelco8/8/2025430792655 272960
Mobile4GResidentialTelco8/8/2025418944864 333116
Mobile4GResidentialTelco8/8/2025420277301 346017
Mobile4GResidentialTelco8/8/2025470265867 366208
Mobile4GResidentialTelco8/8/2025492974711 366208
Mobile4GResidentialTelco8/8/2025404667965 368753
Mobile4GResidentialTelco8/8/2025432708084 391364
Mobile4GResidentialTelco8/8/2025491106335 398313



crm_churn_services

productcategorynameserviceiddisconnectiondategroupnameproductitemnamecustomertypenameaccountnumber
Mobile400336849527/11/2023Telco4GResidential158837
Mobiler1y9h48297627/11/2023Telco4GResidential176495
Mobile991179807627/11/2023Telco4GResidential179660
Mobile670671844027/11/2023Telco4GResidential18682
Mobile1861111022727/11/2023Telco4GResidential231695
Mobilej6p9d22866927/11/2023Telco4GResidential232858
Mobile1870611293027/11/2023Telco4GResidential239486
Mobilec4s3r24125927/11/2023Telco4GResidential240766
Mobilec3j1j25410027/11/2023Telco4GResidential246372
Mobilev7m8t24966127/11/2023Telco5GResidential246379
Mobiles9p3w25504527/11/2023Telco5GResidential250048
Mobile1.00075E+1327/11/2023Telco5GResidential251531
Mobilez8g0b25848727/11/2023Telco5GResidential252208
Mobilek5h2r26876027/11/2023Telco5GResidential259982
Mobilep2g3f27048827/11/2023Telco5GResidential260384
Mobile300697466827/11/2023Telco5GResidential260781
Mobile1014339578927/11/2023Telco5GResidential268725
Mobilec4p5k28180827/11/2023Telco5GResidential270974
Mobileg6c6f28230927/11/2023Telco5GResidential271371
Message 9 of 14
642 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All